Why this Convert has a small quirk?

Question

Why this Convert has a small quirk?

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
June 8, 2010 at 11:27 am

#233468

Hi All,
I was looking at a certain issue and saw that the same cast with convert function gives me two dates when it should be one date. 54 sec difference between the two dates.
select cast((convert(varchar(10), cast('2010-03-12 23:59:58.047' as smalldatetime), 101)) as datetime)
GO
select cast((convert(varchar(10), cast('2010-03-12 23:59:04.047' as smalldatetime), 101)) as datetime)
GO
The first one gives an output of 2010-03-13 00:00:00.000 but the second one gives an output as 2010-03-12 00:00:00.000.
Has anyone seen this behavior?
We usually use SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, '2010-03-12 23:59:58.047')) but there are legacy SP's that still use the CAST and Convert.
-Roy

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Roy it looks to me that this is a rounding issue when the convert is changing to smalldatetime; that is wierd to me as well, since i'd expect the rounding to not be affected at the per-second level;

the breakpoint is anything above 2010-03-12 23:59:30.000 is rounded up to the next smalldatetime;

take a look at the results of this:

declare @results table(TheFullDate datetime,TheSmallDate smalldatetime)

declare @TheDate datetime

set @TheDate = '2010-03-12 23:59:00.000' --or '2010-03-12 23:59:00.047'

while @TheDate < '2010-03-14 00:00:00.000'

BEGIN

insert into @results

select @TheDate,cast((convert(varchar(10), cast(@TheDate as smalldatetime), 101)) as datetime)

SELECT @TheDate = dateadd(ss,1,@TheDate)

END

select * from @results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 2

Hi Roy,

From what I can see, the casting to smallDatetime seems to be the problem. It has an accuracy of 1 minute, therefore, it does round the 57 seconds to the next minute, and then falls on the next day. Taking the 10 first characters to convert to a datetime is the reason you have the wrong day.

What is the behavior you were expecting from this, do you want to use the current day?

Cheers,

J-F

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Hi Lowell,

It is very strange that the rounding is done on second basis. That is what bothered me as well. You would not think that it would happen.

J-F, We have date field along with datetime and when we need to join the two tables based on the date, we usually use DATEADD(dd, 0, DATEDIFF(dd, 0, a.colname)) = b.ColName for example. But there are some legacy stored procs we still need to change and it was using the above mentioned method. Once in whole we would see mismatch in the volume since the currency rate could vary a lot from one day to another.

-Roy

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

Lowell already pointed out where the rounding point happens. But, just as an FYI, BOL says (where ss means Seconds):

ss is two digits, ranging from 00 to 59, that represent the second. Values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.

Cheers!

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 5

Just to confirm... SMALLDATETIME rounds at the 30 second mark. In the last 30 seconds of a given day, it will round up to the next minute which is actually the first minute of the next day.

The general rule would be... don't use CONVERT to do datetime math. Use the DATEADD/DATEDIFF method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

Hi Jeff,

That is what we use but we have some legacy stuff that used these converts. We are changing them all of them.

-Roy

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 7

Hi Roy,

Heh... I had to laugh a bit because I'm right there with you. Seems the phrase "legacy code" usually means "problem code"... even if it was us that wrote it ten years ago. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

Jeff,

Yes, half of it was written after I joined this company... 🙂 I have done some sloppy work as well... hehehe

But I am learning. We started the habit (rule) of reviewing all code that goes to production DB only 3 yrs back. Till then it was Free for all with developers having access to production DB. Those were the days when I was called in almost every weekend to fix cra* that was broken... 😀

-Roy