I have a subquery that gets the
DATEDIFF of two dates. However the first
case always returns
null yet the actual sub query returns dates?!
CASE WHEN [date1] > [date2] THEN DATEDIFF(DD,getdate(),date1) WHEN date1 < date2 THEN DATEDIFF(DD,getdate(),date2) END[date diff] FROM( SELECT licext.new_licenceId, CASE WHEN CONVERT(time,licext.new_EmergencyLicenceExpiryDate) like '%23:00:00.000%' THEN DATEADD(dd,1,CONVERT(date,licext.new_EmergencyLicenceExpiryDate)) ELSE CONVERT(date,licext.new_EmergencyLicenceExpiryDate) END [date1], --EM Code CASE WHEN CONVERT(time,licext.new_standardLicenceExpiryDate) like '%23:00:00.000%' THEN DATEADD(dd,1,CONVERT(date,licext.new_standardLicenceExpiryDate)) ELSE CONVERT(date,licext.new_standardLicenceExpiryDate) END [date2] --FRL from new_licenceExtensionBase licext )QUERY
The reason the second cases are on there is because the database I am working with stores
datetime in a really weird way, ie)If it has 23:00 at the end it actually means tomorrows date?! (This is a Microsoft CRM Database) -But that's a whole different matter.
It did have an ELSE clause which worked fine for date2 being bigger than date1, because that was being evaluated. For Example, the following code works fine for getting the difference between today and date2:
CASE WHEN [date1] > [date2] THEN DATEDIFF(DD,getdate(),date1) ELSE DATEDIFF(DD,getdate(),date2) END[date diff]
I've just done a couple of tests: Using a select to return the dates works fine. -They return as expected.
Test 2: I've added a third
WHEN to the query to say that when a date is null, return 1990-01-01..However it still returns
null, Is my whole case being skipped over?