Introduction To DATETIME Functions In SQL Server

Need for DateTime functions in SQL Server to keep track of date and time has always been a vital part of database design. Most database designs need to track date and time by storing and retrieving it for reporting purposes. Be it an e-commerce website where transaction, sales and purchases need a DateTime. Insurance companies, keeping track of policy expiration and generating sales using audit records stored with DateTime.

An organization needs to do reporting which heavily relies on DateTime details across the database. Database logging with DateTime helps to track and locate error information. Overall, DateTime, essential to a database and needed for reporting, logging, sales and communication.

SQL Server includes various DateTime functions to store and report information accurately. Datetime reporting can be done across various time zones. With various DateTime functions available within SQL server, it is important to understand and distinct use of each one. In this tech-recipes post, we will review some common DateTime functions in SQL Server.

1.

@@DATEFIRST Function In SQL Server

This function shows the first day of the week set in SQL server. The first day of the week is based on language set in SQL Server and can differ based on country. In US English, Sunday is the first day of the week. In Spanish, Monday is the first day of the week.

SET LANGUAGE Spanish;  
GO  
SELECT @@DATEFIRST as FirstDayOfWeek;  --Monday is the First day of the week in Spanish
GO
SET LANGUAGE us_english;  
GO  
SELECT @@DATEFIRST as FirstDayOfWeek; --Sunday is the First day of the week in English

@@DATEFIRST Value Ranges

2.

GETDATE, CURRENT_TIMESTAMP & SYSDATETIME Function In SQL Server

GETDATE function shows a combination of date & time in your time zone, based on the operating system. Moreover, GETDATE function returns fractional seconds in milliseconds (.xxx second) as GETDATE’s return type is DateTime.

SYSDATETIME function also shows date and time in your time zone. It shows fractional seconds in nanoseconds (.xxxxxxx second). SYSDATETIME function’s return type is datetime2 thus SYSDATETIME is more precise than GETDATE.

CURRENT_TIMESTAMP returns DateTime, similar to GETDATE. It’s an ANSI function thus available in other RDBMS like Oracle, MYSQL.

SELECT GETDATE()         AS [GETDATE]
      ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]
      ,SYSDATETIME()     AS [SYSDATETIME];

3.

GETUTCDATE & SYSUTCDATE Time Function In SQL Server

UTC is Universal coordinated time standard, using this world regulates time. Therefore, our local time is different from UTC which is based on world clock and other factors.

GETUTCDATE function shows current DateTime expressed in UTC format. This function uses DATETIME datatype to express time. Thus showing DateTime in milliseconds (xxx second).

SYSUTCDATETIME function shows current DateTime expressed in UTC format. This function uses DATETIME2 datatype to express time. Thus showing DateTime in nanoseconds (xxxxxxx seconds).

SELECT GETUTCDATE()	AS [GETUTCDATE]
      ,SYSUTCDATETIME() AS [SYSUTCDATETIME];

Summary

In this tech-recipes post, we have learnt to use different DATETIME functions in SQL Server. How this function changes datetime based on local time zone and UTC. This will help you to decide correct function based on accuracy and timezone requirement. If you like this post you may walk through Tech-Recipes Database archive post to learn more useful stuff.