SELECT WHERE Date = Day

Question

I have a table with a column of type dateTime. I want to do a query that selects all rows that take place on that date. Basically,

SELECT * FROM Table
WHERE [timeStamp] = '02-15-2003'

But that only returns rows where [timeStamp] is '02-15-2003 00:00:00.000', but really I want rows from anytime that day.

What database are you using? SQL Server? Oracle? PostgreSQL? — Justin Cave, May 10 '12 at 20:16
Strongly recommend you use yyyymmdd and not mm-dd-yyyy. Also suggest giving these a read: https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx http://www.karaszi.com/SQLServer/info_datetime.asp — Aaron Bertrand, May 11 '12 at 15:07

Cade Roux · Accepted Answer · 2012-05-11T15:14:22.057

18

If you have indexes, you are going to want something which doesn't prevent the indexes from being used:

SELECT *
FROM Table 
WHERE [timeStamp] >= '20030215'
      AND [timeStamp] < '20030216'

You can do a truncation operation on the [timeStamp] column to get rid of any time part (implementation dependent), but this can potentially hurt the execution plan. Unfortunately, you really have to look at the execution plan to see this, because sometimes the optimizer is clever about some functions and sometimes it isn't.

edited May 11 '12 at 15:14

answered May 10 '12 at 20:21

Cade Roux

88,164
40
182
265

4

Huraah! One correct answer at last. And have you thought about joining us here on dba.se? http://chat.stackexchange.com/rooms/179/the-heap – gbn May 11 '12 at 06:26
4

Only suggested improvement I'd make is to correct the format - `mm-dd-yyyy` is not language/region safe. I know that's what the OP is using but maybe just needs a nudge. – Aaron Bertrand May 11 '12 at 14:57
1

@Aaron Bertrand I would love it if we would eventually get something to easily handle the various open/closed intervals - something like `datecol WITHIN [lower, upper)` because `BETWEEN` only handles `[]` - there's no concise handling of `()`, `[)`, and `(]` – Cade Roux May 11 '12 at 15:24
Right, but the word BETWEEN is still ambiguous regardless of what syntax extensions you might add to it. People will still expect "between 1/1 and 1/2" to include all data for jan 1 and jan 2 (not ending at midnight on jan 2) and in plain English between is not always inclusive. – Aaron Bertrand May 11 '12 at 23:27

mattmc3 · Answer 2 · 2017-06-13T13:02:16.120

9

You should CAST to DATE if you're on SQL 2008.

select * from [Table]
where cast([timeStamp] as date) = '02-15-2003'

Best approach to remove time part of datetime in SQL Server

--- UPDATE ---

The word the commenters should have used back in 2012 to describe why this is not the optimal solution is sargability. Changing the data type in the WHERE clause, while the simplest solution, has implications for index usage that a bounded search would not.

edited Jun 13 '17 at 13:02

answered May 10 '12 at 20:19

mattmc3

17,595
7
83
103

No functions or CASTs on columns please. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/ – gbn May 11 '12 at 06:26
@gbn - You have a better suggestion? Because there's really not that many ways to strip the time part off of a date in SQL Server, and this way is low friction and easy to type. Always prefer practical over the academic. – mattmc3 May 11 '12 at 13:06
@CadeRoux's answer is better. While I do like yours for SQL Server 2008 the open-ended range is a much safer standard for all versions and all date/time types. – Aaron Bertrand May 11 '12 at 14:56
To be explicit, the open-ended range is generally a better approach because the last approach you should be trying is applying any kind of operation on the base column when it can be avoided. In this case it's ok because SQL Server 2008+ will compensate for your error in the `date` case. :-) – Aaron Bertrand May 11 '12 at 15:20

Royi Namir · Answer 3 · 2012-05-10T20:21:50.697

1

in sql server 2008 + :

  SELECT * FROM Table
        WHERE cast( [timeStamp] as date)  = '02-15-2003'

or

just ZERO the time part : ( 2005+)

   SELECT * FROM Table
    WHERE DateAdd(day, DateDiff(day, 0, [timeStamp]), 0)  = '02-15-2003'

edited May 10 '12 at 20:21

answered May 10 '12 at 20:16

Royi Namir

144,742
138
468
792

No functions or CASTs on columns please. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/ – gbn May 11 '12 at 06:24
2

@gbn the convert to DATE actually doesn't prevent an index from being used, but it should be noted that it doesn't hold true for all type conversions. – Aaron Bertrand May 11 '12 at 14:54

Rusty Nail · Answer 4 · 2016-12-07T20:32:57.333

1

MS SQL 2014, this works perfect:

SELECT [YourDateColumn] FROM [YourTable] WHERE(DATEPART(dd,[YourDateColumn]) = '29')

Might have some performance issues on very large DB's.

edited Dec 07 '16 at 20:32

answered Dec 07 '16 at 20:19

Rusty Nail

2,692
3
34
55

doesn't answer the OP question, but is actually what I was looking for – jgreen Dec 02 '19 at 23:27

score 0 · Answer 5 · answered May 10 '12 at 21:17

0

try this.. SQL SERVER

SELECT * FROM Table WHERE convert(date,[timestamp]) = '2003-02-15'

should return all rows on the specified day.

answered May 10 '12 at 21:17

RoMEoMusTDiE

4,739
1
17
26

try this and you'll see the result. – RoMEoMusTDiE May 11 '12 at 02:06
No functions or CASTs on columns please. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/ – gbn May 11 '12 at 06:26
Oh!!! You might as well tell microsoft to remove all functions. And do it yourself! – RoMEoMusTDiE May 11 '12 at 09:48
1

@Rhian A: Did you read the article? its not never use them, just don't use them on indexed columns becasue then you are no longer using the index. – Chris May 11 '12 at 15:09
2

@RhianA: What @gbn implies is that while the `function(columnX) = constant` will often not be able to use an index. But if you can rewrite as `columnX = reverse_function(constant)` it will. And therefore perform better. – ypercubeᵀᴹ May 11 '12 at 15:10

score 0 · Answer 6 · answered May 11 '12 at 15:33

I would create a stored procedure that will accept "start date" and "end date"

In this case the start date and end date can be the same

This ensures that all rows from 12:01 AM to 11:59 PM are returned

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE  TestBetweenDates
    -- Add the parameters for the stored procedure here
    @StartDate DateTime = 0, 
    @EndDate DateTime = 0
AS
BEGIN

    SET NOCOUNT ON;

        SET @StartDate = cast(Convert(varchar(10), DateAdd(d, -6, @StartDate ), 101) + ' 12:01 AM' as datetime)

        SET @EndDate = cast(Convert(varchar(10), DateAdd(d, -0, @EndDate), 101) + ' 11:59 PM' as datetime)

SELECT * FROM Table
WHERE ([timeStamp] BETWEEN @StartDate AND @EndDate) 


END
GO

score -6 · Answer 7 · answered May 10 '12 at 20:23

-6

Date comparisons can be a tricky thing.

Remember that it is a datetime and not a string. This is why you got unexpected results.

For the specific query you have in mind, the appropriate query is

SELECT * FROM Table 
WHERE 0 = datediff(day,[timestamp],'02-15-2003')

You may also do compares by month() and year() which return integer values.

You usually have to write custom functions to get comparisons that are non-trivial.

Also note

WHERE 0 = datediff(day,[timestamp],'02-15-2003')

is much better than

WHERE datediff(day,[timestamp],'02-15-2003') = 0

The former does not interfere with internal efficiency while the latter does.

answered May 10 '12 at 20:23

mson

7,762
6
40
70

1

I have tested this and you are wrong. The order of clauses in the where does not affect performance. Without an index your version takes twice the amount of time compared to the answers provided by Cade Roux and mattmc3. The real difference in performance in a query like this is when using an index and both queries I mentions does a Index seek where both your queries have to do a full table scan. Until you can provide some tests or documentation that proves me wrong you have a -1 from me. – Mikael Eriksson May 11 '12 at 05:20
1

No functions or CASTs on columns please. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/ – gbn May 11 '12 at 06:26
1

If `WHERE a = b` behaves differently than `WHERE b = a` in any terms, result-wise, performance-wise or any other-wise, I want my money (for the SQL-Server licence) back. – ypercubeᵀᴹ May 11 '12 at 10:25
@mikael - it is a known performance tweak to not put calculated values on the left side of the comparison. as you can see from my profile, i really don't care about the point system - downvote away. – mson May 11 '12 at 13:26
@mson: And I meant `WHERE a = whatever_complex_expression_or_function_b` compared to `WHERE whatever_complex_expression_or_function_b = a` – ypercubeᵀᴹ May 11 '12 at 13:33
3

@mson You say "it's known" but we are telling you **we have tested** and it's not true. There are more myths about SQL Server performance that refuse to die... – JNK May 11 '12 at 13:42
@jnk - i've seen the performance variance demonstrated in 2 different seminars and was able to see the performance improvement when i revised my queries. perhaps, things have changed since sql server 2005, but i will verify again and post if there is no longer a difference. – mson May 11 '12 at 14:08
@gbn yes, but if we have the freedom to assume things, why not create a computed column index - http://www.sqlteam.com/article/using-indexed-computed-columns-to-improve-performance – mson May 11 '12 at 14:12

SELECT WHERE Date = Day

7 Answers7