1

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.

EDIT:

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]

EDIT 2:

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?

  • There is only one explanation for a time of "11:00 pm" being really tomorrow's date - your time-zone is set incorrectly somewhere. Times (and consequently date-times)should always be stored as UTC (or ZULU-time for the dinosaurs) at the server and converted to local time by the client. This is what is broken, not the MS software. – Pieter Geerkens Jul 28 '14 at 22:22
4

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.

My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.

  • Sorry, see my update. – KidCode Jul 28 '14 at 16:02
  • @KidCode That still doesn't cover the possibility of the dates being EQUAL, which is what is happening. Both your date columns use the same calc so they will have the same value. – JNK Jul 28 '14 at 16:08
  • But they don't use the same calculations? They use two totally different columns. I can pretty much guarantee they aren't equal either. Because date1 has 3 values,(the rest being Null) non of which are in date2.(Date2 isn't null) Am I missing something? -Sorry :( – KidCode Jul 28 '14 at 16:10
  • Sorry I missed the column name changes in all that code :) Output the raw dates in a select to see what you get. – JNK Jul 28 '14 at 16:18
  • If date1 is equal to date2 or if date1 is null or if date2 is null, then the CASE expression result will be NULL, just as @JNK answered. – ypercubeᵀᴹ Jul 28 '14 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.