Skip to content
Advertisement

Records are not fetching date wise in SQL Server. 0 records found

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;
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement