issues with datetime to integer value

  • Hi all,

    My issue this time is with cast() function and datetime.

    If I run following querey before and afrer midday 12:00 I will get different values. I tried this with few servers and the result was the same.

    select cast(getdate() as int)

    As I undestood it should return same value for one day regardless off the time.

    question is can I adjust this somehow and why cast is working like this

    Thanks in advance

    Ilkka

  • DATEADD(dd, DATEDIFF(dd, 0, GETDATE() ), 0)

    It remains datetime, but without time portion.

    _____________
    Code for TallyGenerator

  • Sergiy has the right answer, but the reason you have the problem is NOT so much because of the behavior of CAST, but instead is what you're asking CAST to do for you. You asked it to convert a decimal value into an integer, and what you wanted it to do was round down, specifically. Given that CAST provides no means to specify a behavior for that circumstance, it has to be able to do the best it can, which requires rounding down at = .5

    That's why there are date functions such as DATEADD, which can adjust a datetime value by any interval down to the millisecond (in SQL 2005 and before). One other way to resolve the problem would have been:

    CAST(GETDATE() - 0.5 AS int)

    That should work because it will take a 1/2 day away, and at any time from midnight through and including noon, taking away a half day results in either a round up or an exact integer result, while anytime after noon will result in a round down. If you then want to convert back to datetime, it would thus be:

    CAST(CAST(GETDATE() - 0.5 AS int) AS DateTime)

    However, testing shows that for 10,000 records on a quad-core SQL Server 2005 Developer Edition 64-Bit w/SP2 on Vista Ultimate 64-bit w/SP1, the execution time for Sergiy's code was 77ms as opposed to the subtraction of half a day taking 101ms. Thus the 1/2 day adjustment is one third slower than using a date function.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I just realized I was comparing the subtraction WITH the conversion back to DateTime. When I measured just the half-day removal, with no conversion of that integer back to DateTime, it ran just 65ms for 10,000 records, as opposed to the 77ms for the DATEADD & DATEDIFF. FYI...

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I actually read a thread the other day where Jeff was comparing the -.5 as int method with the other 3 main methods, and he seemed to be in favor of the int one. I actually used the integer method in some ad hoc stuff today because I can never remember the exact line for the Dateadd/Datediff method and it worked just fine.

    Granted, that thread was from like 2006 I believe, so I'm not sure if he's changed his viewpoint on that at this point, or if I misread it to begin with. I'll have to track that down.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • I reran my tests with DBCC FREEPROCCACHE in between each of the 3 steps, and got the following results:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (5503716 row(s) affected)

    SQL Server Execution Times:

    CPU time = 734 ms, elapsed time = 25960 ms.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (5503716 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1060 ms, elapsed time = 28718 ms.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    (5503716 row(s) affected)

    SQL Server Execution Times:

    CPU time = 890 ms, elapsed time = 29704 ms.

    Here's the code that produced the 5.5 million records to work with:

    DBCC FREEPROCCACHE

    SET STATISTICS TIME ON

    SELECT CAST(GETDATE() - 0.5 AS int) AS X

    FROM master.dbo.spt_values AS T CROSS JOIN master.dbo.spt_values AS T2

    SET STATISTICS TIME OFF

    DBCC FREEPROCCACHE

    SET STATISTICS TIME ON

    SELECT CAST(CAST(GETDATE() - 0.5 AS int) AS DateTime) AS Y

    FROM master.dbo.spt_values AS T CROSS JOIN master.dbo.spt_values AS T2

    SET STATISTICS TIME OFF

    DBCC FREEPROCCACHE

    SET STATISTICS TIME ON

    SELECT DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0) AS Z

    FROM master.dbo.spt_values AS T CROSS JOIN master.dbo.spt_values AS T2

    SET STATISTICS TIME OFF

    FYI...

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 6 posts - 1 through 5 (of 5 total)

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