More Working with Dates

Question

Post reply

More Working with Dates

Luke L

SSC-Dedicated

Points: 35547
More actions
November 21, 2006 at 2:03 pm

#116347

So in my expirience I've found that the most accurate and fastest way of taking '2006-11-21 13:00:00' to '2006-11-21 00:00:00:000' has been...
SELECT DATEADD(dd, DATEDIFF(dd,0,@mydate), 0)
I was reveiwing some code in an application that I inherited that was malfunctioning, or rather doing what it was supposed to do based on some odd code.
the code I'm fixing uses the convert(datetime, cast(@mydate as int)) method getting a date at midnight. Unfortuantely I've noticed that datetimes after 1200 Noon end up converting to the next day. I know this is because of the cast(@mydate to int) part of the statement. I just want to know more of the why than yes this is what happens.
Example code
DECLARE @mydate datetime,
       @mydate2 datetime
SELECT @mydate = '2006-11-21 11:00', @mydate2 = '2006-11-21 12:00'
SELECT CONVERT(datetime, CAST(@mydate AS INT)) AS convertedDate,
       CAST(@mydate AS INT) AS CasttoInt,
       DATEADD(dd, DATEDIFF(dd,0,@mydate), 0) AS correctMydateMinusTime
SELECT CONVERT(datetime, CAST(@mydate2 AS INT)) AS convertedDate,
       CAST(@mydate2 AS INT) AS CasttoInt,
       DATEADD(dd, DATEDIFF(dd,0,@mydate2), 0) AS correctMydateMinusTime
thanks in advance.
-Luke.
To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

The cast rounds the value. If you convert to float or decimal you will see 39040.5.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

This explains the "why" of what you're seeing:

http://vyaskn.tripod.com/searching_date_time_values.htm

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

That's exactly what I was looking for... I knew there was somethigng silly I was over looking.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

The real question now... why did he design the code that way (business need)?

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

>>why did he design the code that way

I usually see this alternative time stripping method coded using Floor() rather than cast as int. I expect it was a lack of understanding about casting and rounding up.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

From what I can gather he was just dropping the time portion off so that when he displayed it on the page he could just show the date. Unfortunately, since he's no longer with the company I can't really tell for sure. I asked the users and they had never really noticed it until today... imagine that... The data is displayed correctly in another portion of the appliation, so I imagine it just something they've dealt with for the past 4 years the system has been in production.

anyhow, the fix is simple I'll change it to the dateadd(datediff)) method, improve the speed and accuracy of the code and be done with it. I was just not remembering the decimal, and lack of decimal value for an int. I knew it had to be something with rounding and whatnot, I just couldn't rememeber why.

Thanks again guys!

To help us help you read this[/url]For better help with performance problems please read this[/url]

ChrisG-374657 Say Hey Kid Points: 686 More actions · Answer 7

I'm woundering why if you just want to strip off the time you just can't use the convert function with the optional style argument. In this case 101 would be the prefered style. Such as the following:

DECLARE @mydate datetime,

@mydate2 datetime

SELECT @mydate = '2006-11-21 11:00', @mydate2 = '2006-11-21 12:00'

SELECT Convert(datetime,(convert(char(12),@mydate,101))) as correctMydateMinusTime

SELECT Convert(datetime,(convert(char(12),@mydate2,101))) as correctMydateMinusTime

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

The issues are:

- performance

- locale safety

Testing over large data volumes will show that SELECT DATEADD(dd, DATEDIFF(dd,0,@mydate), 0) is the fastest way to strip off time.

Use of string converts can lead you into problems with localization and countries/regions with differing date formats.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Actually, there is one method that's a few milliseconds faster over a million or so rows...

SELECT CAST(CAST(datecolumn-.5) AS INT) AS DATETIME)

As usual, please don't take my word for it... I have a million row test table called "BigTest" and I ran the following code against it...

--===== Declare a couple of operating variables for the test

DECLARE @MyDate DATETIME --Holds the result of a conversion to bypass display times

DECLARE @StartTime DATETIME --For measuring duration of each snippet

PRINT '===== Rounding method 2 ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(CAST((ADate - 0.5 ) AS INTEGER) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== DateDiff/DateAdd method ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=DATEADD(dd,DATEDIFF(dd,0,ADate),0) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Rounding method 1 ======'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(ROUND(CAST(ADate AS FLOAT),0,1) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Convert method ================'

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(CONVERT(CHAR(8),ADate,112) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

PRINT '===== Wrack''s Integer function ====='

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

SET @StartTime = GETDATE()

SELECT @MyDate=CAST(CAST(dbo.DateToDay(Adate) AS CHAR(8)) AS DATETIME) FROM dbo.BigTest

PRINT STR(DATEDIFF(ms,@StartTime,GETDATE()),10) + ' Milliseconds'

PRINT ' '

...and here's the run results...

===== Rounding method 2 ======