January 28, 2009 at 1:53 am
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
January 28, 2009 at 2:23 am
DATEADD(dd, DATEDIFF(dd, 0, GETDATE() ), 0)
It remains datetime, but without time portion.
_____________
Code for TallyGenerator
January 29, 2009 at 7:00 am
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)
January 29, 2009 at 7:10 am
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)
January 29, 2009 at 9:42 am
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.
January 29, 2009 at 10:16 am
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