-1

-- 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!!!

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Shiasu-sama
  • 1,179
  • 2
  • 12
  • 39
  • -- This Does Not Work IF (CONVERT(DATETIME, @MyDateFormatString) = CONVERT(DATETIME, CONVERT(NVARCHAR(MAX), @MyDateFormat))) BEGIN PRINT 'YES!' END ELSE BEGIN PRINT 'NO' END Would really appreciate it if I could find some way to make the above work – Shiasu-sama Aug 15 '17 at 18:00
  • Why would you expect it to work? 7:12:19<>7:12 – Tab Alleman Aug 15 '17 at 18:03
  • xD Just didn't expect SQL to just throw away my seconds into the void – Shiasu-sama Aug 16 '17 at 10:13

1 Answers1

2

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
Simon
  • 1,201
  • 9
  • 18
  • Thank you. Here's why I did not spot that: DECLARE DateTimeAutoConverted DATETIME = CONVERT(DATETIME, '2017-08-15 7:12:19 PM') DECLARE DateTimeAutoConvertedString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), DateTimeAutoConverted) PRINT DateTimeAutoConvertedString SQL is kind enough to drop the seconds when auto converting the string I give it. Any way around that? – Shiasu-sama Aug 15 '17 at 18:08
  • Try with DATETIME2 instead – Jacob H Aug 15 '17 at 18:19
  • @Shiasu-sama Look at my edit for you, and let me know if you have any questions. :-) – Simon Aug 15 '17 at 18:33
  • Hey Thanks. Your workaround is better than mine; I ended up doing something like this WHERE YEAR(CONVERT(DATETIME, [DateOne])) = YEAR(CONVERT(DATETIME, [DateTwo])) AND MONTH(CONVERT(DATETIME, [DateOne])) = MONTH(CONVERT(DATETIME, [DateTwo])) AND DAY(CONVERT(DATETIME, [DateOne])) = DAY(CONVERT(DATETIME, [DateTwo])) which is much more tedious – Shiasu-sama Aug 16 '17 at 10:11