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

Here, you will see higher and lower precision System Date and Time Functions in SQL Server. To define the Date and Time we covered system date and time in-built functions such as Sysdatetime, Sysdatetimeoffset, Sysutcdatetime, Getdate etc that returns the current date and time of the system from the SQL Server. So let's take a look at a practical example of how to use the System Date and Time Functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

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