June 25, 2019 at 2:37 pm
I have a table with a datetime field (END_DATE) that has values like '2019-01-01' and also an integer field (SUB_PROG_ID)
Executing a query with where clause like this
WHERE END_DATE >CAST( '2019-01-01 00:00:00.000' as datetime) AND SUB_PROG_ID <> 0
The issue is that the return values for END_DATE are in the future. Values like '2053-07-05'. However, taking away the AND for the integer field returns correct date values. By correct I mean what is actually in the table.
Never seen this before. See attachments for images.
June 25, 2019 at 2:46 pm
Why do you think your results are wrong ?
Your where clause clearly states that you want all rows with an END_DATE greater than 1st Jan 2019.
And any date in the year 2053 is later than 1 Jan 2019.
Did you mean to use less than ie "END_DATE < CAST(...." ?
June 25, 2019 at 2:47 pm
Well, 2053-07-05 is after 2019-01-01, isn't it?
We don't see the full query, and we don't know how produced the various screenshots, so we can just guess. Either you are doing something funky in the SELECT list, or you really have data for 2053, but you have simply not paid attention to it before.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 25, 2019 at 2:49 pm
There is no data beyond 2019. So how does it return that. There is some weird conversion going on. Here is the full query.
SELECT [SUB_PROG_ID]
,[MEMBER_ID]
,[START_DATE]
,[END_DATE]
,[FEE]
,[DATE_PAY]
,[PAY_SOURCE]
,[PAY_AMT]
,[SESSION_ID]
,[STATUS]
,[USER_LOG]
,[TIEMSTAMP]
FROM [FSS].[dbo].[PROGRAM_PARTICIPANTS]
WHERE END_DATE > '2019-01-01 00:00:00.000' AND SUB_PROG_ID NOT > 0
June 25, 2019 at 2:57 pm
I note that in the screenshot from the query result that SUB_PROG_ID is 436, which does not agree with the WHERE condition.
What is the output from SELECT @@version?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 25, 2019 at 2:59 pm
NVM. I found the issue. Thanks.
June 25, 2019 at 3:10 pm
June 25, 2019 at 3:13 pm
I am embarrassed. Found out that some idiot user had indeed entered bad data.
June 26, 2019 at 7:53 am
I am embarrassed. Found out that some idiot user had indeed entered bad data.
This is why I tend to trust the data first, my preconceptions second, and the users last. The amount of times I've heard "No, we'll never need to store that type of information" or "No, we won't ever do that to the data" and then prove the statement wrong with evidence and be told "Oh, yeah, I suppose we might."or "Well we do in this scenario, it just doesn't happen often." (Once or twice or infinitely more frequent than never. >_< )
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply