Shiasu-sama Shiasu-sama -3 years ago 94
SQL Question

T-SQL Comparing SQL DateTime Variables don't work. Why?

-- Works

DECLARE @MyDateFormatString NVARCHAR(MAX) = '2017-08-15 7:12:19 PM'
PRINT @MyDateFormatString
DECLARE @MyDateFormat DATETIME = '2017-08-15 7:12:19 PM'
PRINT @MyDateFormat
IF (CONVERT(DATETIME, @MyDateFormatString) = CONVERT(DATETIME, @MyDateFormat)) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END


-- Does Not Work

IF (CONVERT(DATETIME, '2017-08-15 7:12:19 PM') = CONVERT(DATETIME, 'Aug 15 2017 7:12PM')) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END


-- WHY!!!

Answer Source

Change

IF (CONVERT(DATETIME, '2017-08-15 7:12:19 PM') = CONVERT(DATETIME, 'Aug 15 2017  7:12PM')) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END

to

IF (CONVERT(DATETIME, '2017-08-15 7:12:19 PM') = CONVERT(DATETIME, 'Aug 15 2017  7:12:19 PM')) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END

or

IF (CONVERT(DATETIME, '2017-08-15 7:12 PM') = CONVERT(DATETIME, 'Aug 15 2017  7:12PM')) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END

on your second query you're not comparing the seconds. On your first query you compare

2017-08-15 7:12 PM

to

Aug 15 2017 7:12PM

Which is correct.

In the second one you compare

2017-08-15 7:12:19 PM

to

Aug 15 2017 7:12PM

7:12:19 is not the same as 7:12. So change either format and it'll be correct.

EDIT:

To address your comment, if you want the DATETIME without seconds use SMALLDATETIME, it will work in your scenario. Have a look at this example using your original second query. BUT BE CAREFUL, it reduces the time format to hh:mm:00, so be wary of this:

IF (CONVERT(SMALLDATETIME, '2017-08-15 7:12:19 PM') = CONVERT(DATETIME, 'Aug 15 2017  7:12PM')) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download