Is there a more efficient method of getting end of day?-CodePudding

I have a DATETIME variable @DateEnd = '1/4/2011 16:43:22 PM'. I want to get the end of day: 1/4/2011 23:59:59.997.

I have the following and it works fine, but it has lots of conversions and doesn't seem efficient:

DECLARE @DateString VARCHAR(25)
DECLARE @DateEnd DATETIME = '1/4/2011 16:43:22 PM'

SET @DateString = CONVERT(VARCHAR(10), @DateEnd, 101)   ' 23:59:59.997'
SET @DateEnd = CAST(DateString AS DATETIME)

Is there a more efficient method of accomplishing this?

CodePudding user response：

Typically what we do is get the 0/midnight value for the start of the next day, and then use an exclusive inequality boundary (<) for that end of the range instead of an inclusive equality boundary (<=).

DECLARE @DateEnd DATETIME = '20110104 16:43:22'
SET @DateEnd = DATEADD(day, 1, Cast(@DateEnd as Date))

It's also NEVER okay to format a datetime literal for SQL like in the question. Different cultures and languages have their own norms and expectations around how dates should look, and it's not good to force your own personal or cultural norms into those languages. SQL, as its own language, is no different. If you're writing SQL and not using SQL's format for the date values (yyyyMMdd, yyyy-MM-ddTHH:mm:ss[.fff], or yyyyMMdd HH:mm:ss[.fff]), you have a WRONG format for that context.

For example, my own preferred format, coming to SQL later after first learning a few other (procedural) languages, is yyyy-MM-dd HH:mm:ss, which doesn't quite match those acceptable formats. I admit I've used that format quite a lot over the years; you can probably find examples in my answers here on Stack Overflow. But every place where I did that was wrong, and I no longer use it. So don't take it too hard; everybody has to learn this.

CodePudding user response：

Probably not more efficient, but I am a fan of using the correct functions for this sort of thing.

Also using the correct datatype, datetime2 is the recommended datatype to use these days, and removes the pesky 3ms interval.

DECLARE @DateEnd DATETIME2(3) = '1/4/2011 16:43:22 PM';

SET @DateEnd = DATEADD(millisecond, -1, CAST(DATEADD(day, 1, CAST(@DateEnd AS DATE)) AS DATETIME2(3)));

CodePudding user response：

One little cheat is format(). However, if @DateEnd is being used as a date range, you may be better off using < TheNextDay

Declare @DateEnd datetime = '1/4/2011 16:43:22 PM'

Set @DateEnd = format(@DateEnd,'yyyy-MM-dd 23:59:59.997')

Just another option avoiding Format()

Declare @DateEnd datetime = '1/4/2011 16:43:22 PM'
Set @DateEnd = concat(convert(date,@DateEnd),' 23:59:59.997')