SQL Server 2005 – Difference between datetime and smalldatetime datatype with example

Here we will discuss about some difference between datetime and small datetime datatype in sql server 2005. We are very much familiar with writing procedure or T-SQL command while doing that we have so many time used date to identify data in table. Also while storing date value, used datetime datatype. Let us find out the difference between datetime and smalldatetime in sql server 2005.

Difference Between Datetime and Smalldatetime

Represent the date and the time of day.
Date time range is in between January 1, 1753 to December 31, 9999
Small datetime range is in between January 1, 1900 to June 6, 2079
The small datetime data type stores dates and times of day with less precision than datetime.

SQL Server stores datetime data type as 8-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1753. The other 4 bytes store the time after midnight.

The Database Engine stores smalldatetime values as 4-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.

Datetime values are rounded to nearest value in milliseconds like if value is in between .002 to .004 than

it will round it as .003. If it exceeds more than .005 to .008 milliseconds than it will round up to .007

smalldatetime values of .999 milliseconds or more are rounded up to the nearest minute.

Example

SELECT ‘DATETIME’ = CAST(‘2012-04-09 12:35:29.999’ AS datetime);

SELECT ‘SMALLDATETIME’ = CAST(‘2012-04-09 12:35:29.999’ AS smalldatetime);

 

Output

DATETIME

———————–
2012-04-09 12:35:30.000

 

SMALLDATETIME
———————–
2012-04-09 12:36:00

 

Here we can see that date time rounded up to nearest value in seconds whereas in small date time when value of seconds more than 997 same has been rounded up in minutes.

 

Leave a Reply

  

  

  


3 + = 11