how to query Datetime object by date?

Question

i want to query a table according to a particular date, but the problem is the datatype of that field is datetime

select  * from  Supplier  where modified_Date='2011-05-07 12:52:16.830'

this query is returning result

but

 select  * from  Supplier  where modified_Date='2011-05-07'

there is no result

score 6 · Accepted Answer · answered May 23 '11 at 09:56

6

SQL Server 2008 has a DATE data type. You can cast your DATETIME to DATE and perform the comparison.

SELECT *
FROM   Supplier
WHERE  CAST(Modified_Date AS DATE) = '2011-05-07'

Reference: DateTime TransactSQL

answered May 23 '11 at 09:56

Lieven Keersmaekers

57,207
13
112
146

score 3 · Answer 2 · answered May 23 '11 at 09:56

3

You have to allow for the time component because 2011-05-07 <> 2011-05-07 12:52:16.830

Either change to a range type query

select  * from  Supplier  where
   modified_Date >= '2011-05-07' 
   and
   modified_Date < '2011-05-08'

...or cast to date since you're using SQL Server 2008

select  * from  Supplier  where
   CAST(modified_Date AS date) = '2011-05-07'

From a performance perspective, use the first one. Or have an indexed computed column that does the CAST for you and you filter on this

answered May 23 '11 at 09:56

gbn

422,506
82
585
676

From a performance perspective SQL Server 2008 will convert the 2nd one into something like the first one anyway. – Martin Smith May 23 '11 at 10:11
@Martin: does it? Can't test right now. Thought of this though https://connect.microsoft.com/SQLServer/feedback/details/653206/the-optimizer-should-be-able-to-understand-the-invertability-of-built-in-intrinsics-on-dates-for-the-purposes-of-index-sargability – gbn May 23 '11 at 10:17
Yes it adds a compute scalar to the plan giving both ends of the range to seek. See http://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable – Martin Smith May 23 '11 at 10:19

Tony · Answer 3 · 2011-05-23T13:38:46.467

1

Just to post an alternative syntax to gbn's answer, you can also use BETWEEN:

SELECT * 
FROM Supplier
WHERE modified_Date BETWEEN '2011-05-07' AND '2011-05-07 23:59:59:999'

EDIT: You have to specify the end time as BETWEEN defaults to midnight which would return additional rows in this case.

From BOL:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

edited May 23 '11 at 13:38

answered May 23 '11 at 11:30

Tony

9,672
3
47
75

`BETWEEN` will return the wrong results. It will include `'2011-05-08 00:00:00` – Martin Smith May 23 '11 at 11:59
Apologies, you are correct, `BETWEEN` will include midnight on the 8th. Have edited my answer to reflect that, it does make the query ugly :) – Tony May 23 '11 at 13:36
Your answer still won't work as `'2011-05-07 23:59:59:999'` gets rounded up to `'2011-05-08 00:00:00:000'`. `Between` is best avoided for this type of query. – Martin Smith May 23 '11 at 15:16
As Nighil is using SQL Server 2008 I was expecting `DATETIME2` to be used (as recommended by MS). To use it with DATETIME change the last 9 to a 7. But this is getting messy so, as you say, it's probably best to avoid `BETWEEN` in this scenario. I was going to delete my answer but I think I'll leave it to help others avoid falling in to the same trap as I did :) – Tony May 23 '11 at 15:26
@Tony - The OP states in the question `datetime`. Even if there were on `datetime2(x)` the most correct thing to use would depend on the value of `x`. For `datetime2(7)` you would need 7 decimal places. – Martin Smith May 23 '11 at 20:32

score 0 · Answer 4 · answered May 23 '11 at 10:00

0

Convert the column into date in your select query will bring the results.

answered May 23 '11 at 10:00

Pankaj

9,749
32
139
283

how to query Datetime object by date?

4 Answers4

Linked