Date Functions

Date functions are used to display information about dates and times. They manipulate datetime and smalldatetime values, performing arithmetic operations on them. Date functions can be used in the select list, in the WHERE clause, or wherever an expression can be used.

Values with the datetime datatype are stored internally by SQL Server 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's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime datatype stores dates and times of day with less precision than datetime. SQL Server 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. Dates range from January 1, 1900, to June 6, 2079, with accuracy to the minute.

The default display format for dates looks like this:

Apr 15 1995 10:23PM

For information about changing the date display format, see "Conversion Function," later in this chapter.

SQL Server recognizes a wide variety of datetime data entry formats. You can use the SET DATEFORMAT statement to set the order of the date parts (month/day/year) for entering datetime or smalldatetime data. When you enter datetime or smalldatetime values, enclose them in single quotation marks.

For a complete list of date functions, see Functions in the Microsoft SQL Server Transact-SQL Reference.