smalldatetime (Transact-SQL)

Defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

Note

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

smalldatetime Description

Syntax	smalldatetime
Usage	DECLARE @MySmalldatetime smalldatetime CREATE TABLE Table1 ( Column1 smalldatetime )
Default string literal formats (used for down-level client)	Not applicable
Date range	1900-01-01 through 2079-06-06 January 1, 1900, through June 6, 2079
Time range	00:00:00 through 23:59:59 2007-05-09 23:59:59 will round to 2007-05-10 00:00:00
Element ranges	YYYY is four digits, ranging from 1900, to 2079, that represent a year. MM is two digits, ranging from 01 to 12, that represent a month in the specified year. DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month. hh is two digits, ranging from 00 to 23, that represent the hour. mm is two digits, ranging from 00 to 59, that represent the minute. 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.
Character length	19 positions maximum
Storage size	4 bytes, fixed.
Accuracy	One minute
Default value	1900-01-01 00:00:00
Calendar	Gregorian (Does not include the complete range of years.)
User-defined fractional second precision	No
Time zone offset aware and preservation	No
Daylight saving aware	No

ANSI and ISO 8601 Compliance

smalldatetime is not ANSI or ISO 8601 compliant.

Examples

A. Casting string literals with seconds to smalldatetime

The following example compares the conversion of seconds in string literals to smalldatetime.

SELECT 
     CAST('2007-05-08 12:35:29'     AS smalldatetime)
    ,CAST('2007-05-08 12:35:30'     AS smalldatetime)
    ,CAST('2007-05-08 12:59:59.998' AS smalldatetime);

Input	Output
2007-05-08 12:35:29	2007-05-08 12:35:00
2007-05-08 12:35:30	2007-05-08 12:36:00
2007-05-08 12:59:59.998	2007-05-08 13:00:00

B. Comparing date and time data types

The following example compares the results of casting a string to each date and time data type.

SELECT 
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' 
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 
        'smalldatetime' 
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 
        'datetime2'
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 
        'datetimeoffset';

Data type	Output
time	12:35:29. 1234567
date	2007-05-08
smalldatetime	2007-05-08 12:35:00
datetime	2007-05-08 12:35:29.123
datetime2	2007-05-08 12:35:29. 1234567
datetimeoffset	2007-05-08 12:35:29.1234567 +12:15

See Also

Reference

CAST and CONVERT (Transact-SQL)