1

I exported records and I want to know which record was exported on a particular day. It does not work. Here is the data if I query it.

* comapny Name *     * date exported *
ABC Company, Inc    2011-08-01 15:44:52.857
XYZ Company, Inc    2011-08-01 15:44:52.857

I issue this command which does not retrieves the exact matches

select companyname, exporteddate from mytable exporteddate = '2011-08-01' <- does not work
select companyname, exporteddate from mytable exporteddate like '%2011-08-01%' <-- tried this variation too and many other, did not work

The interesting thing is >=, >, <= works. What really is the problem? The exported date is declared as datetime field.

select companyname, exporteddate from mytable exporteddate >= '2011-08-01' <- this works

I am using Windows XP, MS-SQL 2005 SP3 (not exactly but close).

Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
TheTechGuy
  • 16,560
  • 16
  • 115
  • 136

7 Answers7

5

= '2011-08-01' will only match datetimes of exactly midnight on that date (i.e. any rows you have that have values of 2011-08-01 00:00:00.00).

The best way of doing the query is where exporteddate >= '20110801' and exporteddate < '20110802'

This is sargable, avoids ambiguous datetime formats and is better than the BETWEEN alternative with an end condition on 20110801 23:59:59.997 as that relies on an implementation detail about the max precision of the datetime datatype that will break if you move over to the new SQL Server 2008 datetime2 datatype at some later stage.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

Your problem is that you're using a DATETIME field to store a DATE. As you can see, the data is actually stored down to milliseconds.

To get this to work, you can:

  1. Search on a range of DATETIME values

    WHERE exporteddate BETWEEN '2011-08-01 00:00:00.000' AND '2011-08-01 23:59:59.999'

  2. Convert your storage from DATETIME to DATE (the simplest solution if you don't really need to distinguish multiple loads on single date).

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • 1
    `23:59:59.999` doesn't work. This gets rounded up. The max precision of `datetime` is 300 ticks per second so `SELECT cast('2011-08-01 23:59:59.999' as datetime)` gives `2011-08-02 00:00:00.000` – Martin Smith Sep 23 '11 at 19:28
  • Apologies, thinking of another engine entirely (and their DEXTRACT function). I was thinking of CAST as DATE but, with no DATE type in SQL Server 2005 as per Martin, above, that isn't available. Your stuck with the range search. – Larry Lustig Sep 23 '11 at 19:39
0

A datetime field with no time specified is interpreted as a date with a time of 00:00:00.

Phil Sandler
  • 27,544
  • 21
  • 86
  • 147
0

unless you specify, the time part will be exactly 00:00:00 so all those seconds in your timestamp will prevent exact match.

Randy
  • 16,480
  • 1
  • 37
  • 55
0

Date fields don't work like text fields.

Your first non-working select doesn't include time (which is in the data). You would need to trunc the date or convert it to a string using a format that only has year/month/day.

Same for the LIKE statement. Its not a text field.

Date's however can be checked for equality using >, <, etc as you see.

Brett McCann
  • 2,469
  • 2
  • 27
  • 44
0

Like many other suggest, because you don't supply time the SQL will use 00:00:00 instead. To compare a DATE to a DATEIME you may convert your DATETIME to a DATE.

I don't know how to achive this in SQL server but in MySQL it would be : select companyname, exporteddate from mytable DATE(exporteddate) = '2011-08-01'

Draco
  • 1
0

Try this, you are converting both dates to same format and comapring them. 

select companyname, exporteddate from mytable  CONVERT(VARCHAR,exporteddate ,103)= 

CONVERT(VARCHAR,'2011-08-01',103);
Hari Gillala
  • 11,736
  • 18
  • 70
  • 117