Post reply

Date question

  • July 20, 2015 at 7:50 am

    #298899

    Hello, I have what I hope is a very simple issue, but I can't work it out.

    In management studio, a date value appears as:

    2015-07-20 14:50:20.000

    I'm ok about formatting that to appear as 20 July 2015 but, when I put in my WHERE query,

    Date >=2015-07-20

    All date values are coming through, starting from 2009! Is there any way I can add a statement to just bring through dates from a particular period, e.g. today's date or between last week and this week?

    Any help appreciated, thanks.

    Thanks

  • July 20, 2015 at 7:54 am

    #1813649

    You need to add quotes to your date (and if possible remove the dashes).

    Date >= '20150720'

    The reason for returning older dates is that without quotes, SQL Server identifies the value as an integer operation and converts the value to a datetime somewhere around the mid 1900's.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 20, 2015 at 7:56 am

    #1813651

    If you run these two select statements it will give you a clue as to where the issue is. (as well how to fix it) 😉

    SELECT CAST(2015-07-20 AS DATETIME)

    SELECT CAST('2015-07-20' AS DATETIME)

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • July 20, 2015 at 7:56 am

    #1813653

    Yes, because without quotes that's getting treated as a mathematical expression, specifically as two numbers being subtracted from 2015.

    2015 - 07 - 20 = 1988

    The calculated value of 1988 is then implicitly cast to datetime, the way that work is the number is treated as days since 1900. 1988 days after the first of January 1900 is the 12th of June 1905

    Therefore your predicate is evaluated as

    Date >= '1905-06-12'

    which is probably not what you meant...

    Put your date in single quotes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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