Post reply

Help in using date range

  • January 5, 2016 at 11:49 am

    #399527

    Select * from activity where product_code like '%WELL%' and transaction_date>='2013-01-01'

    This query works, it gives me all activities from 1/1/2013 for WELL ACTIVITIES

    But my goal is to use date range

    For example Select * from activity where product_code like '%WELL%' and transaction_date>='2013-01-01' and <='2014-12-31'

    Im getting syntax error, what am I doing wrong, please advise.

    Regards,
    SQLisAwe5oMe.

  • January 5, 2016 at 11:56 am

    #1849632

    AND in this case separates filter conditions. <='2014-12-31' by itself is not a valid condition. You need this for the last two conditions:

    transaction_date>='2013-01-01' and transaction_date<='2014-12-31'

    Cheers!

    EDIT: Inside code tags my greater than and less than signs were getting transformed, so removed the code tags.

  • January 5, 2016 at 12:19 pm

    #1849638

    Thanks Jacob, that worked.

    I also tried this way and it worked as well, just FYI.

    Select * from activity where product_code like '%WELL%' and transaction_date between '2013-01-01' and '2014-12-31'

    Thanks again.

    Regards,
    SQLisAwe5oMe.

  • January 5, 2016 at 2:20 pm

    #1849681

    SQLisAwE5OmE (1/5/2016)

    Thanks Jacob, that worked.

    I also tried this way and it worked as well, just FYI.

    Select * from activity where product_code like '%WELL%' and transaction_date between '2013-01-01' and '2014-12-31'

    Thanks again.

    Consider changing the date range test to AND transaction_date >= '20130101' AND transaction_date < '20150101'

    The version you use will cut off at Dec 31st, midnight - so if your datatype is datetime or smalldatetime or datetime2, you will mess entries with a datestamp such as e.g. Dec 31st 3:45PM. The version I use still does not include Jan 1st, 2014 - but it does include all of Dec 31st. Regardless of the data type used for the transaction_date column.

    (Even when your data has data type date and this does not matter, you should still get into the habit of testing your date ranges that way - if it's an automatism, you will never get it wrong).

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 5, 2016 at 2:36 pm

    #1849684

    seems reminiscent of this post

    http://www.sqlservercentral.com/Forums/FindPost1747707.aspx

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

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

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