The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions, see Using Date and Time Data.
The Transact-SQL date and time data types are listed in the following table.
time
hh:mm:ss[.nnnnnnn]
00:00:00.0000000 through 23:59:59.9999999
100 nanoseconds
3 to 5
Yes
No
date
YYYY-MM-DD
0001-01-01 through 9999-12-31
1 day
3
smalldatetime
YYYY-MM-DD hh:mm:ss
1900-01-01 through 2079-06-06
1 minute
4
datetime
YYYY-MM-DD hh:mm:ss[.nnn]
1753-01-01 through 9999-12-31
0.00333 second
8
datetime2
YYYY-MM-DD hh:mm:ss[.nnnnnnn]
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
6 to 8
datetimeoffset
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)
8 to 10
The Transact-SQL date and time functions are listed in the following tables. For more information about determinism, see Deterministic and Nondeterministic Functions.
All system date and time values are derived from the operating system of the computer on which the instance of SQL Server is running.
SQL Server 2008 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.
SYSDATETIME
SYSDATETIME ()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
datetime2(7)
Nondeterministic
SYSDATETIMEOFFSET
SYSDATETIMEOFFSET ( )
Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.
datetimeoffset(7)
SYSUTCDATETIME
SYSUTCDATETIME ( )
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time).
CURRENT_TIMESTAMP
GETDATE
GETDATE ( )
GETUTCDATE
GETUTCDATE ( )
DATENAME
DATENAME ( datepart , date )
Returns a character string that represents the specified datepart of the specified date.
nvarchar
DATEPART
DATEPART ( datepart , date )
Returns an integer that represents the specified datepart of the specified date.
int
DAY
DAY ( date )
Returns an integer that represents the day day part of the specified date.
Deterministic
MONTH
MONTH ( date )
Returns an integer that represents the month part of a specified date.
YEAR
YEAR ( date )
Returns an integer that represents the year part of a specified date.
DATEDIFF
DATEDIFF ( datepart , startdate , enddate )
Returns the number of date or time datepart boundaries that are crossed between two specified dates.
DATEADD
DATEADD (datepart , number , date )
Returns a new datetime value by adding an interval to the specified datepart of the specified date.
The data type of the date argument
SWITCHOFFSET
SWITCHOFFSET (DATETIMEOFFSET , time_zone)
SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.
datetimeoffset with the fractional precision of the DATETIMEOFFSET
TODATETIMEOFFSET
TODATETIMEOFFSET (expression , time_zone)
TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.
datetimeoffset with the fractional precision of the datetime argument
@@DATEFIRST
Returns the current value, for the session, of SET DATEFIRST.
tinyint
SET DATEFIRST
SET DATEFIRST { number | @number_var }
Sets the first day of the week to a number from 1 through 7.
Not applicable
SET DATEFORMAT
SET DATEFORMAT { format | @format_var }
Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.
@@LANGUAGE
Returns the name of the language that is currently being used. @@LANGUAGE is not a date or time function. However, the language setting can affect the output of date functions.
SET LANGUAGE
SET LANGUAGE { [ N ] 'language' | @language_var }
Sets the language environment for the session and system messages. SET LANGUAGE is not a date or time function. However, the language setting affects the output of date functions.
sp_helplanguage
sp_helplanguage [ [ @language = ] 'language' ]
Returns information about date formats of all supported languages. sp_helplanguage is not a date or time stored procedure. However, the language setting affects the output of date functions.
ISDATE
ISDATE ( expression )
Determines whether a datetime or smalldatetime input expression is a valid date or time value.
ISDATE is deterministic only if you use it with the CONVERT function, when the CONVERT style parameter is specified, and when style is not equal to 0, 100, 9, or 109.
Using Date and Time Data
Provides information and examples that are common to date and time data types and functions.
CAST and CONVERT (Transact-SQL)
Provides information about the conversion of date and time values to and from string literals and other date and time formats.
Writing International Transact-SQL Statements
Provides guidelines for portability of databases and database applications that use Transact-SQL statements from one language to another, or that support multiple languages.
ODBC Scalar Functions (Transact-SQL)
Provides information about ODBC scalar functions that can be used in Transact-SQL statements. This includes ODBC date and time functions.
Data Type Mapping with Distributed Queries
Provides information about how date and time data types affect distributed queries between servers that have different versions of SQL Server or different providers.
print replace(str(DatePart(hh, GetDate()), 2), ' ', '0') + ':' + replace(str(DatePart(mi, GetDate()), 2), ' ', '0') + ':' + replace(str(DatePart(s, GetDate()), 2), ' ', '0')
Prints the current time like:
15:04:20
Refer to my blog for SQL Server date range enumeration table valued function sql date range function.