0

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

Shandhanu Mohan
  • 129
  • 7
Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322
  • 1
    You should convert formats with [`CONVERT`](http://msdn.microsoft.com/en-us/library/ms187928.aspx) – Alexander Feb 26 '14 at 11:42
  • 3
    Well - are there any rows where time equals 02/26/2014? And if so, is your SQL Server is set up to handle dates in that format? What if you try to change the query to: '20142602' AND '20142602' – Allan S. Hansen Feb 26 '14 at 11:43
  • 1
    Don't use `BETWEEN` here anyway. Unless the intention is to include rows which have exactly midnight on 26 Feb but no other times on that date. – Martin Smith Feb 26 '14 at 11:45
  • @MartinSmith, then what should I do? Allen, yes there are rows – Imran Qadir Baksh - Baloch Feb 26 '14 at 11:46
  • and what does this return: `SELECT DISTINCT PV.[Time] From PV WHERE PV.[Time] BETWEEN '02/25/2014' AND '02/27/2014'` - do your time columns have a time component? – Nick.Mc Feb 26 '14 at 11:48
  • Is PV.[Time] inclusive a times segment? Because if so, and you want to use between you'll need to include the time in the between. – Allan S. Hansen Feb 26 '14 at 11:49
  • @AllanS.Hansen, I don't want to include time. – Imran Qadir Baksh - Baloch Feb 26 '14 at 11:51
  • 1
    @user960567 in reference to MartinSmith's comment about inclusion of rows for an inclusive date, I've used the CAST CEILING trick with great success, thus excluding the time `CAST(CEILING(CAST(@MyDate AS FLOAT)) AS DATETIME)`. Floor also works. See here http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server – Tom Feb 26 '14 at 11:52
  • cast your PV.[Time] to a date without time segment – Allan S. Hansen Feb 26 '14 at 11:52
  • @Tom - That isn't sargable. – Martin Smith Feb 26 '14 at 11:53
  • @AllanS.Hansen - [That isn't optimal](http://dba.stackexchange.com/questions/34047/cast-to-date-is-sargable-but-is-it-a-good-idea) – Martin Smith Feb 26 '14 at 11:54
  • @MartinSmith Would you recommend the DateAdd method instead? – Tom Feb 26 '14 at 11:56
  • @Tom - Neither. The time component is in the values in the `PV.[Time]` column. Applying almost any function to a column makes it unsargable (can't use an index). Casting to date is kind of sargable but not as efficient as simply doing the range seek on `>= ... <` – Martin Smith Feb 26 '14 at 11:59
  • @MartinSmith thanks for the explanation, I learnt something today! – Tom Feb 26 '14 at 12:03

2 Answers2

2

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'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

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');
Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47
  • You were right, @MartinSmith, I was aware of this edge-case, but did not consider as significant. The code was corrected to be completely correct. – Gerardo Lima Feb 26 '14 at 12:04