In the recent past, I have been bringing few puzzles around using date and time functions. All these have brought some awesome feedbacks and you all have been sharing some awesome answers. Today’s question comes from an unusual place wherein one of a developer said he was getting unexpected results with working with datetime. So here is how the conversation went:

**Dev:** Hi Pinal.

**Pinal:** Yes, buddy. How can I be of help?

**Dev:** I have a problem working with datetime datatype.

**Pinal:** Oh is it? Tell me more about it.

**Dev:** I am trying to CAST a string coming from a front end app to SQL Server.

**Pinal:** Ok, where is the problem?

**Dev:** Every now and then, it gives different results than what I try to send via the wire.

**Pinal:** I am not able to understand. Can you send me an example to understand this better?

**Dev:** Sure, I will send via email.

After I received the actual code and email, I resumed the conversation.

**Pinal:** Hey.

**Dev:** Yes, Sir. Did you find the reason?

**Pinal:** I found the reason and I am going to blog about it tomorrow. It is a simplified version of the query you sent me. So you are about to find the answers there. The hint is: you are doing a convert to smalldatetime

**Dev:** Well, thanks. I think I will do my homework and try to answer it in your blog too.

This concept was interesting and I planned to share this experience with you folks like a small puzzle. So what are we talking?

Look at the below query and guess what would be the output:

`SELECT`

CAST('2015-01-01 12:45:29.755' AS SMALLDATETIME),

CAST('2015-01-01 12:45:35.755' AS SMALLDATETIME)

This is a simple conversion but the two values are different. Why is that? Do you know the reason? Use the comments section to explain this. I may send surprise gift to 2 of the winners.

Reference: **P****inal Dave (https://blog.sqlauthority.com)**

## 46 comments. Leave new

Smalldatetime rounds seconds off…

SMALLDATETIME rounds the timestamp to the nearest minute; therefore, the results are:

2015-01-01 12:45:00 and 2015-01-01 12:46:00.

I didn’t get what error is facing by developer but,

if you run in sql 2014 output will be,

2015-01-01 12:46:00

2015-01-01 12:45:00

respectively,

SMALLDATETIME does not contains seconds if seconds are provides in casting string it will round the date i.e. ignore a seconds and adjust minute

12:45:29.755 Rounded up 12:45 and 12:45:30.000 on wards rounded to 12:46

The reason is becouse the accuracy of smalldatetime type is one minute. So that first answer is 45 and second is 46 minutes after noon

small date time – discards the seconds by rounding up or rounding down any second greater than zero

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers to an accuracy of one three-hundredth of a second.The smalldatetime data type stores dates and times of day with less precision than datetime with accuracy to the minute. SQL Server stores smalldatetime values as two 2-byte integers. This is the reason why we see difference of a minute.

Reference: https://www.microsoft.com/en-us/download/details.aspx?id=51958

SELECT

CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME), — 2015-01-01 12:45

CAST(‘2015-01-01 12:45:35.755’ AS SMALLDATETIME) –2015-01-01 12:46

Getting a result is easy by running this query however reason for rounding up and down is because of precision of smalldatetime which has a minimum precision of 1 minute. Since value in first cast statement is less than 29.998 seconds it is getting rounded down to nearest minute while in second example value is greater than 29.998 second it’s getting rounded up to 46 minute.

2 years back I was stuck into another problem of rounding where ROUND function in sql server takes 3rd optional parameter which we generally aren’t aware of. I got a business requirement where i had to truncate data instead of rounding after two decimal places. While I was using round function with two parameters. So it was an interesting solution to discover.

SMALLDATETIME has a precision of 1 minute – first one is rounded down (29 seconds), second one up (bigger than half a minute 35 sec)

SELECT

CAST(‘2015-01-01 12:45:29.755′ AS SMALLDATETIME), — 2015-01-01 12:45

CAST(‘2015-01-01 12:45:35.755′ AS SMALLDATETIME) –2015-01-01 12:46

Getting a result is easy by running this query however reason for rounding up and down is because of precision of smalldatetime which has a minimum precision of 1 minute. Since value in first cast statement is less than 29.998 seconds it is getting rounded down to nearest minute while in second example value is greater than 29.998 second it’s getting rounded up to 46 minute.

2 years back I was stuck into another problem of rounding where ROUND function in sql server takes 3rd optional parameter which we generally aren’t aware of. I got a business requirement where i had to truncate data instead of rounding after two decimal places. While I was using round function with two parameters. So it was an interesting solution to discover.

Hi Pinal,

smalldatetime simply rounds to the 30 Seconds. That’s why if the seconds are more than 30 then it increments the minute part.

Sir its reason CAST(‘2015-01-01 12:45:35.755’)—-> second column —-> time part wrote 12:45:35<———— this 35 is main reason if you write here <30 value then it gives same answer

The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.

I do not have better answer than this :-)

https://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/

https://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/ :) “The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.”.

https://blog.sqlauthority.com/2010/06/01/sql-server-precision-of-smalldatetime-a-1-minute-precision/ :) “The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero. “

Hi pinal,

smalldatetime reveals the date with accuracy of minute. Smalldatetime values with 29.998 seconds or lower are rounded to the previous nearest minute and 29.999 seconds or higher are rounded to the next nearest minute.

Smalldatetime will have only hours and minutes. While casting to it that will round off to nearest minute. I.e. Below 29 seconds will fall under previous minute and above will fall under upper minute

Reason behind this is

“The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.”

I forgot to add about how rounding works..

Values from 00 to 59 represents 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.

SmallDateTime is only accurate to the nearest minute. In the first one, the value is rounded down to 12:45; the second value is rounded up to 12:46.

Hi,

As per my knowledge, Date time rounded to nearest minute after 30 sec on wards.

It display respectively.

2015-01-01 12:45:00 2015-01-01 12:46:00

From the result, we can understand that It round off the seconds.

1)2015-01-01 12:45:29.755 — 2015-01-01 12:45:00 ( Sub 30 seconds )

2)2015-01-01 12:45:35.755 — 2015-01-01 12:46:00 ( Add 30 seconds )

Thanks,

Vaibhav Shukla

Hi Pinal

This is because converting to smalldatetime data type rounds of the minute by discarding the seconds. So output will be “2015-01-01 12:45:00” and “2015-01-01 12:46:00” respectively.

E:g –

SELECT

CAST(‘2015-01-01 00:01:29.999’ AS SMALLDATETIME), CAST(‘2015-01-01 00:01:29.998’ AS SMALLDATETIME)

Thanks

Sudhev

According to the query it gives two different result as we are using SMALLDATETIME for casting. In SQL server datatype SMALLDATETIME values with 29.998 seconds or lower are rounded down to the nearest minute.

So the first one will be rounded to ‘2015-01-01 12:45:00’ and second will be rounded to ‘2015-01-01 12:46:00’ as it has more than 30 seconds.

Thanks

From specification of smalldatetime

….

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute……

The reason is because there is difference in the seconds. The one which has more than 30 seconds converts it into the next minute and the one with less than 30 seconds converts it into the same minute.(We can relate it to round off function)

cast in smalldatetime converts datetime into 24Hr format. And it will always have seconds as 0. Also it will always convert any second more then 29 will be rounded off by adding a minute in time and any second less then 29 will be rounded off by subtracting a minute from time.

A SMALLDATETIME value is stored as two two-byte integers. The first integer represents the date, and the second integer represents the time.The time does not reflect the number of seconds, and if seconds are included in the value, they are rounded 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.

Because the precision of the SMALLDATETIME datatype is 1 minute. Seconds are discarded by rounding.

I mean, rounded up or down, depending on the value of the seconds

The reason these values are different is due to the seconds element for smalldatetime. Values that are 29.998 seconds or less are rounded down to the nearest minute while values of 29.999 seconds or more are rounded up to the nearest minute.

Sir it’s working like a rounding function and rounding it, like 12:45:29.755 will be rounded to 12.45 as ms part of time is 30.

Hey… In smalldatetime data type, “second” values that are 29.99 seconds or less are rounded down (floor) to the nearest minute, where values of 29.99 seconds or more are rounded up to the nearest minute (ceiling). In this case, 12.45.29, the second 29 is actually 29.00000 then rounded down to 12:45, where 12:45:35, 35 seconds is greater than 29.99 so it is rounded up to 12:46. Cheers.

2015-01-01 12:45:00 2015-01-01 12:46:00

That is the output i got when i ran the SQL statement. I think if the seconds greater than 30 then it is going to the next minute when rounding.

smalldatetime datatype returns date/time value with accuracy to a minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Looks to me like a rounding difference. SmallDateTime doesn’t go to millisecond, so SQL Server has to choose which minute to display. Looks to me like it does simple rounding to the minute, but it ignores the millisecond component and just rounds based on the full seconds.

The reason why is because if the seconds are more than 30 than the time is rounded up in minutes and if the seconds are less than 30 than the minutes are rounded down. This is shown on msdn website: https://msdn.microsoft.com/en-us/library/ms182418.aspx

smalldatetime rounds up or down based on the seconds. So 2015-01-01 12:45:29.755 would round down, and 2015-01-01 12:45:35.755 will round up to 12:46. Similarly, 2015-01-01 12:45:30.755 would round up to 12:46 as well.

As the msdn say, the format use a 24 hour time with 00 as seconds always and 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.

SmallDateTime is rounding to mm:ss (30 seconds and above rounded to next minute). Hence we are getting different results.

The time part in SMALLDATETIME contains seconds as always zero (:00) and without fractional seconds. If we store/assign any fractional seconds to smalldatetime value, it discards the seconds by rounding up or rounding down.

If seconds are =30 then rounds up

In this way the results vary whenever we try to cast datetime values with seconds to smalldatetime…

SELECT

CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME), –2015-01-01 12:45:00

CAST(‘2015-01-01 12:45:35.755’ AS SMALLDATETIME), –2015-01-01 12:46:00

CAST(‘2015-01-01 12:45:30.000’ AS SMALLDATETIME), –2015-01-01 12:46:00

CAST(‘2015-01-01 12:45:30.755’ AS SMALLDATETIME), –2015-01-01 12:46:00

CAST(‘2015-08-03 23:59:59.000’ AS SMALLDATETIME) — 2015-08-04 00:00:00

Regards,

Chandrika

Smalldatetime datat type has an accuracy upto minutes. Also, 29.998 or lower seconds are rounded down to the neareset minutes and 29.999 and above seconds are rounded up to the nearest minute value. This is the reason why CAST(‘2015-01-01 12:45:29.755’ AS SMALLDATETIME) rounds to the “2015-01-01 12:45:00” as second part 29.755 is lower than 29.998 and

CAST(‘2015-01-01 12:45:30.755’ AS SMALLDATETIME) rounds to “2015-01-01 12:46:00” as second’s part 30.755 is greater than 29.999.

Dear pinal m waiting for your response

The precision of the datatype SMALLDATETIME is 1 minute. It discards the seconds by rounding up or rounding down any seconds greater than zero.

Since seconds has 60 strikes, its neutral value is 30. I believe

Round up will happen when the value is above 29

Round down will happen when the value below 30

Since seconds has 60 strikes, its neutral value is 30. I believe

Its minute round up will happen when the second’s value is above 29

Its minute round down will happen when the second’s value below 30