CODEWITHSUNDEEP
sqldatesql-server-2012
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33944

Between Date is not working in SQL SERVER

I have a very simple SQL which select data from a range of date. So, for example if I have,

SELECT ...... WHERE PV.[Time] BETWEEN '02/26/2014' AND '02/26/2014'

This SQL Select statement is not selecting the data in 02/26/2014. Please help

Upvotes: 0

Views: 1544

Answers (2)

Gerardo Lima
Gerardo Lima

Reputation: 6703

It's always preferable to use a non-dubious string format for dates (1/2/2000 can be interpreted as 01-feb-2000 or 02-jan-2000, depending on your local settings). I prefer the ISO format yyyymmdd or ODBC canonical yyyy-mm-dd and always use CONVERT to be explicit when handling dates. But this is not your problem :)

The problem with your query is that you are actually filtering dates BETWEEN '02/26/2014 00:00:00' AND '02/26/2014 00:00:00', thus you'll only get values at exactly the datetime. To get datetime values through whole day 02/26/2014 use the following:

SELECT ...
WHERE PV.[Time] >= '2014-02-26' AND PV.[Time] < dateadd(day, 1, '2014-02-26');

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452957

Your current statement BETWEEN '02/26/2014' AND '02/26/2014' has the same value on the left and right and so is equivalent to = '02/26/2014'.

This will only bring back rows at midnight on 26 Feb. Use

 WHERE PV.[Time] >= '20140226' AND PV.[Time] < '20140227'

Upvotes: 2

Related Questions