I want to get the records from Dec-1-2019 to Dec-31-2019 but I am not getting any records from the database while checking with out date filter the records are coming. There are records in the database table between these days but not coming.
Query
SELECT * FROM DATA WHERE DEVICEID = '786423' AND DeviceTimeStamp BETWEEN CONVERT(DATE, '12/1/2019 12:00:00 AM', 103) AND CONVERT(DATE, '1/1/2020 12:00:00 AM', 103) ORDER BY DeviceTimeStamp ASC
Sample data
2019-12-03 11:53:25.000 944.5 2019-12-03 11:38:07.000 944.5 2019-12-03 11:22:59.000 944.2 2019-12-03 11:07:09.000 943.9
C# Code
fromdate = Convert.ToDateTime(txtFrm.Text); 12/1/2019 12:00:00 AM todate = Convert.ToDateTime(txtTo.Text); datatype - datetime, null
Advertisement
Answer
Consider specifying an ISO 8601 datetime literal. That will be independent of the session DATEFORMAT
setting and eliminate the need for CONVERT
:
SELECT * FROM DATA WHERE DEVICEID='786423' AND DeviceTimeStamp BETWEEN '2019-12-01T00:00:00' AND '2020-01-01T00:00:00' ORDER BY DeviceTimeStamp;
You might also consider specifying an inclusive start date and exclusive end date:
SELECT * FROM DATA WHERE DEVICEID='786423' AND DeviceTimeStamp >= '2019-12-01T00:00:00' AND DeviceTimeStamp < '2020-01-01T00:00:00' ORDER BY DeviceTimeStamp;