Is there an easier way to set a DateTime in SQL Server to be 23:59:59

Question

Good Morning All,

I'm trying to refactor an SQL stored procedure. I'm no SQL expert, but something tells me there must be a better way to do this.

IF @ipv_dtEndDate IS NOT NULL
    BEGIN
        SET @ipv_dtEndDate = DATEADD(hh,23,@ipv_dtEndDate)
        SET @ipv_dtEndDate = DATEADD(mi,59,@ipv_dtEndDate)
        SET @ipv_dtEndDate = DATEADD(ss,59,@ipv_dtEndDate)
    END

This value is used later inside a WHERE clause. These filters seem difficult to understand to me. I was hoping to come up with a cleaner implementation.

AND qtrh.StatusTime <= IsNull(@ipv_dtEndDate, qtrh.StatusTime)

And this date calculation...

AND DATEDIFF(ss,qtrh.StatusTime,ISNULL(@dtNow,DATEADD(ss,-1,qtrh.StatusTime))) < DATEDIFF(ss,ISNULL(@dtDateOptionCompare,GETDATE()),GETDATE())

... seems quite convoluted and unreadable. If any SQL gurus out there have some suggestions on how I can improve this, I would love to hear some ideas. Thanks for your time. Have a terrific holiday weekend.

Cheers,
~ck in San Diego

Answer 1

You could convert the date to varchar, add your "23:59:59" and then convert it back to datetime

Answer 2

How about this?

SET @ipv_dtEndDate = CONVERT(varchar, @ipv_dtEndDate, 101) + ' 23:59:59'

Answer 3

If the only use of @ipv_dtEndDate is inside the Where clause, you could remove the entire IF @ipv_dtEndDate IS NOT NULL block, and replace the condition in the SQL query with:

AND qtrh.StatusTime < DATEADD(dd,1,IsNull(@ipv_dtEndDate, qtrh.StatusTime))

(Strictly speaking, you will now also be including StatusTime values between 23:59:59 and 00:00:00, which were previously excluded.)

Answer 4

If you feel uncomfortable with the varchar approach, you can do the following.

SET @ipv_dtEndDate = DATEADD(ss, DATEDIFF(ss, 0, '11:59:59'), @ipv_dtEndDate)

Answer 5

Usually I use < and the date for the next day rather than trying to run a <+ with the last second of midnight.

Answer 6

To convert a date without a time (rather, with the time set to "midnight the morning of") to the "end of the day", you can just add the number of seconds:

DECLARE @ipv_dtEndDate datetime

SET @ipv_dtEndDate = 'Sep 3, 2010'
PRINT convert(varchar(50), @ipv_dtEndDate, 109)  --  Before

SET @ipv_dtEndDate = dateadd(ss, 1439, @ipv_dtEndDate)
PRINT convert(varchar(50), @ipv_dtEndDate, 109)  --  After

Of course, SQL datetime is accurate to the [333rd of a] millisecond, so the end of the day is actually:

DECLARE @ipv_dtEndDate datetime

SET @ipv_dtEndDate = 'Sep 3, 2010'
PRINT convert(varchar(50), @ipv_dtEndDate, 109)  --  Before

SET @ipv_dtEndDate = dateadd(ms, 1439997, @ipv_dtEndDate)
PRINT convert(varchar(50), @ipv_dtEndDate, 109)  --  After

Using the built in (and mathematically based) date/time functions will be more efficient than converting to character strings and back.