Bobski Bobski - 29 days ago 18
SQL Question

SQL querying with DATES - not pulling correct data

I'm running a simple query where I am trying to filter by a date, like this...

Select * from tblPurchasedProducts
WHERE PurchasedOn >= '11/04/2016' AND PurchasedOn <= '11/08/2016'


But I am not able to see any results for 11/8/2016. I'm only able to see everything prior to 11/08/2016.

The date is saved in SQL SERVER as smalldatetime. I'm really not sure if this has anything to do with it.

Is the time an issue in this case.

I am required to save the PurchasedOn date with as datetime

EDIT:

@lamak
WHERE PurchasedOn >= '20161104' AND PurchasedOn <= '20161108'

Answer

You need to be careful with your conditions. By using <= '11/08/2016' it's using the 00:00:00 time, that's why you are not getting results. Use:

SELECT * 
FROM tblPurchasedProducts
WHERE PurchasedOn >= '20161104' AND PurchasedOn < '20161109';