Strange results from date field

  • 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.

     

     

     

    • This topic was modified 4 years, 9 months ago by  cxxxv.
    • This topic was modified 4 years, 9 months ago by  cxxxv.
    Attachments:
    You must be logged in to view attached files.
  • 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(...." ?

     

    • This reply was modified 4 years, 9 months ago by  nigel..
  • 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]

  • 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
  • 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]

  • NVM. I found the issue. Thanks.

  • cxxxv wrote:

    NVM. I found the issue. Thanks.

    Which was .... ?

  • I am embarrassed. Found out that some idiot user had indeed entered bad data.

  • cxxxv wrote:

    I am embarrassed. Found out that some idiot user had indeed entered bad data.

    Ahah! Users ( ******!!) who needs 'em ? 🙂

    • This reply was modified 4 years, 9 months ago by  nigel.. Reason: Smile!
  • cxxxv wrote:

    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