Higher and Lower Precision System Date and Time Functions in SQL Server 2012

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