Query date and time data
-
I need to query data for a specific date in a table with datetime data type.
I wrote the following but it gives zero:
select *
from test
where chkdate like '2004-01-06'
How I should find it?
Thanks for the help in advance.
-
Try something like this:
WHERE ChkDate LIKE 'Jan 6 2004%'
--Jonathan -
There are various ways....
WHERE ChkDate BETWEEN '1/6/2004' AND '1/6/2004 23:59:59.999'
This may perfom much better especially if ChkDate is indexed and the optimizer thinks its useful to use.
Also, a while back there was a thread about doing something like
WHERE ChkDate = ChkDate and ChkDate BETWEEN '1/6/2004' AND '1/6/2004 23:59:59.999'
possibly making the query much faster by having the datetime column compare itself to itself prior to the actual date range filtering.
Once you understand the BITs, all the pieces come together
-
I guess not everyone's asleep.
There are two good reasons for not using LIKE with temporal values, even though it's recommended in BOL.
- In order to use a predicate with LIKE, and non-string value must be converted to a string. This means, in this case, that the column must be converted, thus making the expression non-sargable so an index cannot be used. If your column will contain a non-zero time component (i.e. all your times are not midnight), then you should use an expression that is sargable, like WHERE ChkDate >= '20040106' AND ChkDate < '20040107'. This will allow an index seek on the column (if an index is placed on it). If you have guaranteed that there are no non-zero time components, then just use WHERE ChkDate = '20040106'. If you wish to be very unambiguous and not rely on the implicit conversion from string to datetime (but notice how high the temporal types are in the data type priorities), then you could even go so far as to explicitly convert any constants to the same data type as the column, e.g. WHERE ChkDate = CAST('20040106' AS datetime).
- Using LIKE with temporal columns can expose the code to internationalization issues. Notice my use of the ISO standard date formats above, and how that differs from the English format of the string used with the LIKE expression in my previous post (and Thomas' equivocal format). LIKE, unfortunately, only works with the "Default" display format for temporal data types.
Thomas' predicates are incorrect, as the larger constant will be rounded up to the next day. This is because the time part of datetime values is acually in clock ticks, not milliseconds, so '20040106 23:59:59.999' = '20040107 00:00:00.000' = '20040107 00:00:00.001'.
--Jonathan -
Try this
select * from test where convert(char, chkdate, 112) = '20040106'
But this will not use any index.
If you want the query to use an available index on chkdate column, you may use the following:
select * from test where chkdate between '20040106' and dateadd(s, -1, '20040107')
SQL server will treate all strings of 'yyyyMMdd' format as valid datetime values with the time as midnight. So, dateadd(s, -1, '20040107') means last second of 6th Jan 2004
-
Create Function DateOnly(@dt DateTime)
returns Datetime
as
begin
return Cast(convert(varchar(8),@dt,1) AS datetime)
end
select * from test where dbo.Dateonly(chkdate) = '2004-01-06'
This way you can use this at any time
Rohit -
> select * from test where chkdate between '20040106' and dateadd(s, -1, '20040107')
This might work with smalldatetime values, but it will possibly exclude some datetime values for that date. See my predicates using >= and < rather than BETWEEN.
> select * from test where dbo.Dateonly(chkdate) = '2004-01-06'
Three problems with this:
- By using a conversion style that uses only two digits for years, you risk using the wrong century. This is determined by the two digit year cutoff setting, where the default is 49. Try this: SELECT dbo.Dateonly('19481025').
- By using a function on the column values, you are making the predicate non-sargable.
- Using a UDF is slower than just using inline functions, which are simple enough for such an operation.
--Jonathan -
We do this so often we finally put a 2nd field ChkDateOnly in the table, and use
CAST(CONVERT(CHAR(10),ChkDate,101) AS DATETIME)
in the insert trigger to load it with a "pure date" - a copy of the date in question with a time value of 00:00:000.
The ChkDateOnly field is indexed so we get fast, acruate results from WHERE ChkDateOnly = '2004-01-06'.
This dropped several of our queries from 15 minutes to 15 seconds and we can all remember how to do it at 2:00 AM.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply