How to Search for Date and Time Values

  • bryansyverson

    SSC Rookie

    Points: 36

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp


    Regards,
    Bryan

  • pmanisekaran

    Old Hand

    Points: 341

    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.

  • pmanisekaran

    Old Hand

    Points: 341

    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%'

  • ckempste

    SSCoach

    Points: 17983

    Hi

    Outstanding article that covers off some classic issues, thanks for spending the time writing it.

    Cheers

    Chris K

    http://www.chriskempster.com


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Jonr

    SSCrazy

    Points: 2566

    Most excellent article Brian, one of those reference ones that will go into the very useful solutions folder. Thank you.

    Jon Reade


    Jon

  • bryansyverson

    SSC Rookie

    Points: 36

    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

  • JohnMarks

    Ten Centuries

    Points: 1113

    Nice article. Just put your suggestions on how to calc time to good use for a report I had to write. Thanks

  • bambola

    Grasshopper

    Points: 24

    <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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply