Char to DateTime Seconds Rounding

Question

Char to DateTime Seconds Rounding

rootfixxxer

SSCrazy

Points: 2918
More actions
November 24, 2011 at 8:53 am

#252545

Hello
I was testing here some data with dates, but then i realize that sql server rounds the seconds, when converting from a string to a datetime/smalldatetime.
For example i made this simple query:
DECLARE
@Data1 SMALLDATETIME,
@Data2 SMALLDATETIME
-- Direct conversion
SET @Data1 = '12/31/2011 23:59:29'
SET @Data2 = '12/31/2011 23:59:30'
SELECT @Data1
SELECT @Data2
-- CONVERT
SET @Data1 = CONVERT(DATETIME,'12/31/2011 23:59:29')
SET @Data2 = CONVERT(DATETIME,'12/31/2011 23:59:30')
SELECT @Data1
SELECT @Data2
-- CAST
SET @Data1 = CAST('12/31/2011 23:59:29' AS DATETIME)
SET @Data2 = CAST('12/31/2011 23:59:30' AS DATETIME)
SELECT @Data1
SELECT @Data2
In every variation i get always the same results.
When the seconds are 29 (@Data1), i get this date: 2011-12-31 23:59:00
When the seconds are 30 (@Data2), i get this date: 2012-01-01 00:00:00
What's the "problem"/logic behind this?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

rootfixxxer (11/24/2011)
Hello
I was testing here some data with dates, but then i realize that sql server rounds the seconds, when converting from a string to a datetime/smalldatetime.
What's the "problem"/logic behind this?
Thanks

SMALLDATETIME is accurate to the nearest 1 minute. As you declared your variables to be SMALLDATETIME, they are rounded to the nearest minute, which you can see at the top of your code: -

DECLARE @Data1 SMALLDATETIME

,@Data2 SMALLDATETIME

-- Direct conversion

SET @Data1 = '12/31/2011 23:59:29'

SET @Data2 = '12/31/2011 23:59:30'

SELECT @Data1

SELECT @Data2

If instead we use DATETIME. . .

DECLARE @Data1 DATETIME

,@Data2 DATETIME

-- Direct conversion

SET @Data1 = '12/31/2011 23:59:29'

SET @Data2 = '12/31/2011 23:59:30'

SELECT @Data1

SELECT @Data2

You'll see it returns 2011-12-31 23:59:29.000 and 2011-12-31 23:59:30.000

This is because DATETIME is accurate to the nearest 0.000, 0.003, or 0.007 second so doesn't round.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rootfixxxer SSCrazy Points: 2918 More actions · Answer 2

Oh Sh*** :blush:

I just changed the date time in the first var forgot the second one, so i didn't see any difference! The code that i posted here was the original code!

Solved