SQL date comparison

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  January 21, 2015 at 9:54 am

  #296802

  Hi,

  The date I have in the table is:

  2015-01-08 20:41:54.000

  when I write a query to compare the date in WHERE stmt to a specific date without time portion in there

  Select invoiceDate from table1

  where InvoiceDate = '1/8/2015'

  or

  Select invoiceDate from table1

  where InvoiceDate like '1/8/2015%'

  When I do this, it doesn't return anything. Please advise.

  Thanks

  Dee

• Andrew Kernodle

  SSCertifiable

  Points: 5314

  More actions

  January 21, 2015 at 10:00 am

  #1771908

  I believe what's happening there is that the date of 1/8/2015 that you're searching for is being expanded; therefore, the actual value the query is searching for is 1/8/2015 00:00:00.000.

  To catch everything that happened on 1/8/2015, try this:

  WHERE InvoiceDate >= '1/8/2015' AND InvoiceDate < '1/9/2015'

  This will do a search from 1/8/2015 00:00:00.000 to 1/8/2015 23:59:59.997, if you're using the DATETIME datatype for InvoiceDate; using DATETIME2 will make the search even more accurate, if desired.

  In any case, that will fetch everything from the 8th; typically, you'll be needing to search between a range to get everything related to a specific date.

  -

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  January 21, 2015 at 10:05 am

  #1771910

  Thank you. It works.

  Dee