T-SQL query error while datetime conversion

Question

T-SQL query error while datetime conversion

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

sqlnaive SSCoach Points: 17437 More actions · Answer 1

Interestingly, what I found is suppose in Table overall I have 1000 records and after all the joins with other tables I get 200 records and in those 200 records, I'm getting BusinessDate as either proper dates or 99999999. So the condition in my view is

case when businessdate = 99999999 then cast('99991231' as datetime)

However in the rest of 800 records in my table there is another value which is 99999990, which as per my understanding should not impact the view but it's actually doing so. when I make my condition as follows, it works fine:

case when businessdate > 99991231 then cast('99991231' as datetime)

Now my understanding is that SQL parses the SELECT criteria at last and verifies everything only on the records it gets after the joins. So what I'm missing here ?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

sqlnaive (5/17/2016)
Interestingly, what I found is suppose in Table overall I have 1000 records and after all the joins with other tables I get 200 records and in those 200 records, I'm getting BusinessDate as either proper dates or 99999999. So the condition in my view is
case when businessdate = 99999999 then cast('99991231' as datetime)
However in the rest of 800 records in my table there is another value which is 99999990, which as per my understanding should not impact the view but it's actually doing so. when I make my condition as follows, it works fine:
case when businessdate > 99991231 then cast('99991231' as datetime)
Now my understanding is that SQL parses the SELECT criteria at last and verifies everything only on the records it gets after the joins. So what I'm missing here ?

SQL Server is mostly free to evaluate criteria in any order it thinks is most efficient, so long as they would yield the same results.

With that extra information, it seems like you are indeed in exactly the situation I described in my first response on this thread. 🙂

Cheers!

EDIT: That value would also have been caught by my query, so I'm guessing you ran it against some view or JOINed result set instead of directly against the underlying table.

sqlnaive SSCoach Points: 17437 More actions · Answer 3

Jacob Wilkins (5/17/2016)
sqlnaive (5/17/2016)
Interestingly, what I found is suppose in Table overall I have 1000 records and after all the joins with other tables I get 200 records and in those 200 records, I'm getting BusinessDate as either proper dates or 99999999. So the condition in my view is
case when businessdate = 99999999 then cast('99991231' as datetime)
However in the rest of 800 records in my table there is another value which is 99999990, which as per my understanding should not impact the view but it's actually doing so. when I make my condition as follows, it works fine:
case when businessdate > 99991231 then cast('99991231' as datetime)
Now my understanding is that SQL parses the SELECT criteria at last and verifies everything only on the records it gets after the joins. So what I'm missing here ?
SQL Server is mostly free to evaluate criteria in any order it thinks is most efficient, so long as they would yield the same results.
With that extra information, it seems like you are indeed in exactly the situation I described in my first response on this thread. 🙂
Cheers!
EDIT: That value would also have been caught by my query, so I'm guessing you ran it against some view or JOINed result set instead of directly against the underlying table.

I definitely should have read your post more carefully. Bad on my side. But yes, that was causing the issue. Strange but true. Will keep this experience with me forever... 🙂