Higher-Precision System Date and Time Functions
Sysdatetime()
The Sysdatetime function returns a datetime value that contains the current date and time of the system. This function does not contain time zone offset with date and time.
Example
-- SYSDATETIME Function
select SYSDATETIME() as [SYSDATETIME Function]
Output
Sysdatetimeoffset()
The Sysdatetimeoffset function returns a datetimeoffset value that contains the current date and time of the system. This function includes the time zone offset with date and time.
Example
-- Sysdatetimeoffset Function
select Sysdatetimeoffset() as [Sysdatetimeoffset Function]
Output
Sysutcdatetime()
The Sysutcdatetime function returns a datetime value that contains the current date and time of the system. The date and time is returned as UTC time (Coordinated Universal Time).
Example
-- Sysutcdatetime Function
select Sysutcdatetime() as [Sysutcdatetime Function]
Output
Lower-Precision System Date and Time Functions
Current_timestamp
The Current_timestamp function returns a datetime value that contains the date and time of the system. This function does not contain time zone offset with date and time.
Example
-- CURRENT_TIMESTAMP Function
select CURRENT_TIMESTAMP as [CURRENT_TIMESTAMP Function]
Output
Getdate()
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. This function does not includes time zone offset with date and time.
Example
-- Getdate Function
select Getdate() as [Getdate Function]
Output
Getutcdate()
Returns a datetime 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).
Example
-- Getutcdate() Function
select Getutcdate() as [Getutcdate Function]
Output
DAY (date) - Returns the day of the month as an integer from date.
Example
-- Day() Function
SELECT DAY('04/15/2011') AS 'Day Number'
SELECT DAY(GETDATE()) AS 'Day Number'
Output
MONTH (date) - Month Function returns the month as an integer from date.
Example
-- MONTH Function
SELECT MONTH ('04/15/2011') AS 'MONTH Number'
SELECT MONTH (GETDATE()) AS 'MONTH Number'
Output
YEAR (date) - Year function returns the 4-digit year as an integer from date.
Example
-- Year Function
SELECT Year('04/15/2011') AS 'Year Number'
SELECT Year(GETDATE()) AS 'Year Number'
Output