How to Search for Date and Time Values
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Regards,
Bryan -
Why was "dtColumn like dt +'%' " not mentioned as one of the ways seraching for dates in s datetime column with inconsistent storage like storing date and time.
-
hi abhi_develops,
I mean something like this, it obviously is not an efficient way. Just another method
ex:
SELECT CONVERT(varchar, dateEntered, 103)
FROM dbo.tblDirectory
WHERE CONVERT(varchar, dateEntered, 103) LIKE '09/07/2002%'
-
Hi
Outstanding article that covers off some classic issues, thanks for spending the time writing it.
Cheers
Chris K
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Most excellent article Brian, one of those reference ones that will go into the very useful solutions folder. Thank you.
Jon Reade
Jon -
Thanks for your comments.
You're right about the omission. However, as with most search expressions, there are many working solutions. I didn't mention
WHERE CONVERT(varchar, dateEntered, 103) LIKE '09/07/2002%'
nor did I mention
WHERE CAST(CAST(dateEntered AS char(11)) AS datetime) = '2002-09-07'
(I do show a few of these in Chapter 8 of my book, however.)
As you mentioned, the LIKE version will perform badly, perhaps even worse than the other version that just use functions. I figured there's no reason to include multiple poorly-performing solutions.
Regards,
Regards,
Bryan -
Nice article. Just put your suggestions on how to calc time to good use for a report I had to write. Thanks
-
<quote>
Datetime and smalldatetime are like the floating-point data types, float and real, in that they’re approximate numerics. That means the value retrieved from SQL Server may be different from the value that was originally stored.
</quote>
Reading the datetime as float will mostly result the correct values, but not always.
DECLARE @d datetime
SELECT @d = '1900-01-24 13:12:10.990'
SELECT @d as_datetime, convert(datetime, cast(@d as float)) as_float
Results are:
1900-01-24 13:12:10.990 as_datetime, 1900-01-24 13:12:10.987 as_float.
You can see that casting datetime to float and back result a value different from the initial one.
The reason is simple: Datetime and smalldatetime are not really floating point datatype. They are actually 2 4-bytes integers (or 2 2-bytes integers in case of smalldatetime). One represents the number of days past since 1900-01-01. The other, contains the number of clock ticks past since midnight (1 second = 300 clock ticks).
To calculate the integer containing the datepart, we could use the datediff function:
-- Calculate numner of days since 1900-01-01:
SELECT datediff(dd, 0, @d) as number_of_days
This will result 23 days.
Calculating the time part is trickier. We have to first calculate the number of second past since midnight, multiply is by 300 clock ticks and add the fraction of the milliseconds:
-- Calculate the number of clock ticks:
SELECT
(
datepart(hour,@d) * 60 * 60
+ datepart(minute,@d) * 60
+ datepart(second,@d)
) * 300
+ cast(datepart(ms,@d)/1000.*300 as int) as number_of_clock_ticks
This results 14259297 clock ticks.
Now let's try to read the 2 integers that are composing the datetime. I will cast the variable as binary(8), then convert to int the first 4 byes that represent the date part, and the last 4 bytes that represent the time part:
SELECT cast(substring(convert(binary(8), @d),1, 4) as int) date_part_in_int,
cast(substring(convert(binary(8), @d),5, 8) as int) time_part_in_int
The results are 23 days and 14259297 clock ticks which are the exact same values calculated above.
Note that for this example I used .990 as milliseconds to avoid rounding problems. Milliseconds are always rounded to .000 .003 or .007 in following way:
.990 .991 --> .990
.992 .993 ..994 --> .993
.995 .996 .997 .998 --> .997
.999 --> .000
DECLARE @d1 datetime, @d2 datetime
SELECT @d1 = '2002-02-02 23:59:59:998', @d2 = '2002-02-02 23:59:59:999'
SELECT @d1, @d2
Results: 2002-02-02 00:00:00.000 2002-02-03 00:00:00.000
This assignment might not have much sense. You are not likely to find yourself asigning
998 milliseconds to a datetime variable. But if you do, you can see that rounding occures before asigning the value. From this point on the value retrieved will never be different from the one asigned.
To see the difference between the conversion to float and int, try to run this script:
SET NOCOUNT ON
DECLARE @x datetime
DECLARE @t table
( base_date datetime
, as_float datetime
, as_binary binary(8)
)
DECLARE @i int
SELECT @x = '1900-01-01 13:12:10.999'
SELECT @i = 1
SELECT @x = dateadd(millisecond,@i,@x)
WHILE @i <= 10
BEGIN
SELECT @x = DATEADD(millisecond,@i,@x)
INSERT INTO @t
SELECT @x
, convert(datetime,cast(@x as float))
, convert(binary(8),@x)
SELECT @i = @i + 1
END
SELECT
base_date
, as_float
, case when as_float <> base_date then 'WRONG' else 'OK' end ok_f
, convert(datetime, as_binary) binary_to_date
, case when convert(datetime, as_binary) <> base_date then 'WRONG' else 'OK' end ok_b
FROM @t
There is not doubt that there are limitations to the datetime variable. I would have liked, for example, to be able to store values before 1753. I also cannot understand why millisecond are rounded and not stored correctly. However, as long as you are reading datetimes correctly, the system always returns the same value stored.
If you choose to refer to datetime at lower level, and avoid having the datetime algorytm run on all values (and I do it in some cases since performance can improve dramatically!), you must read it as 2 integers acording to the shown above. This is what it is - 2 integers. Not a float. 🙂
A few words about searching. SQL Server does does not have seperate datatypes for date and time. And as long as this is the case (and from what I have heard in the next version there will be), it would be wrong to refer to datetime as date only.
When searching a date range, we have to make sure our time portion is set correctly. If time is irelevant, start_date time portion should be set to '00:00:00: and the end_date to '23:59:59.997'.
This can be done in serveral ways
- convert(varchar(10), start_date, 120)
(ISO is always the best way to handle dates)
- cast( substring(cast(start_date as binary(8), 1, 4) as datetime)
not that intuitive, but works much better than the above.
- dateadd(dd,datediff(dd,0,start_date ),0)
This is probably the most elegant and efficient way.
To set the end_date, you should first set the time part to zero, then add a day and substruct 2 milliseconds.
- dateadd(millisecond, -2, dateadd(dy,1,end_date_00))
I hope this shades some light on the datetime variable.
Bambola.
Edited by - bambola on 03/07/2003 04:30:07 AM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply