Help in using date range

• SQLisAwe5oMe

  SSChampion

  Points: 14052

  More actions

  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.

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  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.

• SQLisAwe5oMe

  SSChampion

  Points: 14052

  More actions

  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.

• Hugo Kornelis

  SSC Guru

  Points: 64790

  More actions

  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/
  

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  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