Date and Time (Transact-SQL)

Switch View

Date and Time (Transact-SQL)

Community Content

In this section

Statistics Annotations (2)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

Date and Time (Transact-SQL)

Are data types that are used for representing the date and the time of day.

datetime and smalldatetime

Represent the date and the time of day.

Data type	Range	Accuracy
datetime	January 1, 1753, through December 31, 9999	3.33 milliseconds
smalldatetime	January 1, 1900, through June 6, 2079	1 minute

Remarks

Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine stores smalldatetime values as two 2-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 increments of .000, .003, or .007 seconds, as shown in the following table.

Example	Rounded example
01/01/98 23:59:59.999	1998-01-02 00:00:00.000
01/01/98 23:59:59.995, 01/01/98 23:59:59.996, 01/01/98 23:59:59.997, or 01/01/98 23:59:59.998	1998-01-01 23:59:59.997
01/01/98 23:59:59.992, 01/01/98 23:59:59.993, or 01/01/98 23:59:59.994	1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or 01/01/98 23:59:59.991	1998-01-01 23:59:59.990

smalldatetime 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.

--Returns time as 12:35.
SELECT CAST('2003-05-08 12:35:29.998' AS smalldatetime);
GO
--Returns time as 12:36.
SELECT CAST('2003-05-08 12:35:29.999' AS smalldatetime);
GO

Tags

: datatype (x) date (x) datetime (x) time (x) Add a tag

Community Content

Add new content

Annotations

Important - CLR DateTime to SQL Server Roundtrip Inaccuracy

LukeSkywalker | Edit | Show History

If you persist a CLR DateTime value to SQL, when re-read out of SQL back into a CLR DateTime variable it will no longer match the one in memory. This can lead to subtle dataset corruptions.

http://seesharper.wordpress.com/2008/07/08/sql-server-datetime-vs-net-datetime-battle-of-accuracy/

I have an application where I store in RAM whatever goes into my db by pulling it back out which entraps it in the cache as part of my get-entity-fill procedure. To ease the burden on SQL, I use LINQ to query the cache in-memory, but I was finding missing data because the dates are a couple of ticks out.

Reference

Other Resources

Help and Information