While filtering date time to date am not getting any result

Question

I have query like this:

select empid,transDateTime
from Trans_Details
where empId='16510'
  and transDateTime >='2015-05-07 00:00:00.000'
  and transDateTime< ='2015-05-07 23:59:59.000'

I'm getting output like this:

empid           transDateTime
--------------- -----------------------
16510           2015-05-07 08:51:56.000

I have same query without time, but that one doesn't return any result:

select empid, transDateTime
from Trans_Details Td
where td.empId='16510'
  and Td.transDateTime='2015-05-07'

What's the issue? This time I want to get same result.

Don't compare to `<= 23:59:59` - there is a chance that data can fall in the 1 second `crack` between that and midnight. Use `Td.transDateTime >= '2015-05-07' AND Td.transDateTime < '2015-05-08' ` — StuartLC, Jul 09 '15 at 06:44

Felix Pamittan · Accepted Answer · 2015-07-09T06:51:01.110

2

This is because transDateTime is of DATETIME type. On your WHERE clause, 2015-05-07 is converted into DATETIME and thus results in 2015-05-07 00:00:00.000.

You can use this instead:

WHERE CAST(transDateTime AS DATE) = '20150507'

Note that you should use YYYYMMDD format for date literals.

Another method way so that you avoid using a function in the left-side of your WHERE clause is:

WHERE 
    transDateTime >= CAST('20150507' AS DATETIME)
    AND transDateTime < DATEADD(DAY, 1, CAST('20150507' AS DATETIME))

edited Jul 09 '15 at 06:51

answered Jul 09 '15 at 06:42

Felix Pamittan

31,544
7
41
67

this time how i can fetch vale from this date? – jas backer Jul 09 '15 at 06:43
I'd give you -1 for the first solution and +1 for the 'alternate method'. Putting a function on the field you're filtering on IS bad practice. But as @StuartLC pointed out below, the `convert(date, ...)` is automagically translated into a `@1 <= field AND field < @2` construction, which I had no clue about, so learned something new again today. However, tried this on a table with couple of millions of rows (SQL2012) and the alternate method did it in 4ms while the first one takes 8ms so in the end I would still advice against the Cast(date, ...) method, every little bit counts! =) – deroby Jul 09 '15 at 08:21
`CAST`ing to `DATE` is `SARGABLE`. See [this](http://dba.stackexchange.com/a/34052/63605). – Felix Pamittan Jul 09 '15 at 08:25

score 2 · Answer 2 · answered Jul 09 '15 at 06:43

2

Try this

select empid,transDateTime from Trans_Details Td where td.empId='16510' 
and convert(date,Td.transDateTime)='2015-05-07'

answered Jul 09 '15 at 06:43

SimarjeetSingh Panghlia

2,200
13
14

2

`CONVERT(DATE, ..)` is one of the few [SARGable functions](http://stackoverflow.com/a/10854024/314291) (?Monotonic) but in general it isn't a good idea to apply a function to a column in a predicate. – StuartLC Jul 09 '15 at 06:49

score 0 · Answer 3 · answered Jul 09 '15 at 07:13

0

Better you can CAST both side

WHERE CAST(transDateTime AS DATE) = CAST('2015-05-07' AS DATE)

answered Jul 09 '15 at 07:13

Jesuraja

3,774
4
24
48

score 0 · Answer 4 · answered Jul 09 '15 at 07:52

The second query will not work because '2015-05-07' will get converted to datetime as '2015-05-07 00:00:00.000' and then compared for exact equality.

Other solutions suggested here using cast on transDateTime column will work but has a disadvantage: you won't be able to use an index involving that column for the query. So if you have lots of data in that table (think millions of rows), then it's better to stick to your first solution which is more verbose, but performance-wise far better (if you have indexed that column). A slight modification for correctness:

select empid, transDateTime
from Trans_Details
where empId = '16510'
  and transDateTime >= '2015-05-07 00:00:00.000'
  and transDateTime <  '2015-05-08 00:00:00.000'

While filtering date time to date am not getting any result

4 Answers4