SQL Server Date Handling

Working with dates in sql is one of the most challenging parts of data manipulation. In this blog we aim to describe some of the complexities of date handling in SQL Server.

Data Types

Prior to Microsoft SQL Server 2008 the only data types available were datetime or the smalldatetime. With the launch of SQL Server 2008 saw the release of date, datetime2, datetimeoffset, and time. These data types brought additional benefits and can reduce the amount of coding linked to managing date and time data.

The details of these data types can be found in the Microsoft documentation and is in the table below. In terms of optimising performance the byte size is something to note. The other takeaway is the increased performance of datetime2 vs datetime, and the benefit of using smalldatetime if seconds are not required.

The output of the original data types are below:

The “new” data types are below.

System Dates

With an under standing of the date formats. What system dates exist for date handling in SQL Server. The following formats are available.

1. GETDATE(): Returns the current date and time of the system on which the SQL Server instance is running.
2. SYSDATETIME(): Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
3. SYSDATETIMEOFFSET(): Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running, including the time zone offset.
4. SYSUTCDATETIME(): Returns a datetime2(7) value that contains the Coordinated Universal Time (UTC) date and time of the computer on which the instance of SQL Server is running.
5. CURRENT_TIMESTAMP: Returns the current date and time of the system on which the SQL Server instance is running.

A sample of key dates are below. Getdate has a precision of 3, whilst sysdate has 7.

SYSDATETIMEOFFSET allows offset time, which is ideal for timezones. Whilst Current_Timestamp and Sysutcdatetime mirror, getdate and sysdatetime precision respectively.

SQL Functions

Formatting dates from source to output is a key to data manipulation. Handling of dates from Excel, flat files or ETL pipelines are key challenges. Here are some of the functions in SQL to format dates,

Cast

The syntax for cast is:

CAST ( expression AS data_type [ ( length ) ] )

This powerful function but relies on the expression been in the correct format for the data type.

Convert

There are a few options to transform data in SQL Server. The original option was the Convert statement. The basic syntax is below.

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The most used for UK dates include:

SELECT CONVERT(varchar, getdate(), 23)  formats as YYYY-MM-DD

SELECT CONVERT(varchar, getdate(), 1)  formats as DD/MM/YY

A full list of the convert statements are below

Note 130 and 131 are Islamic data types

Format Function

This function was introduced in SQL Server 2012 and is similar to Oracle’s to_date function. This is much more flexible. The format function is flexible and is not just used for dates.

The syntax is below:

FORMAT(value, format, culture)

In this:

Value – must be a value based on certain data types

Format – this is pattern based on a format pattern. More details are below.

Culture – an optional argument allowing a Language to be set.

An example is:

FORMAT (getdate(), ‘dd-MM-yy’) as date

Values

The following data systems are relevant to dates

• date
• time.
• datetime.
• smalldatetime
• datetime2
• datetimeoffset

Format

The following formats are available:

• yy – this is the year with two digits
• yyyy – this is the year with four digits
• dd – this is day of month from 01-31
• d – this is day of month from 1-31 (on its own it will display the entire date)
• dddd – this is the day spelled out
• MM – this is the month number from 01-12
• MMM – month name abbreviated
• MMMM – this is the month spelled out
• hh – this is the hour from 01-12
• HH – this is the hour from 00-23
• mm – this is the minute from 00-59
• ss – this is the second from 00-59
• tt – this shows either AM or PM

Culture

Some sample cultures are below. A fill list can be found here.

Conclusion

Working with dates in sql can be a challenge for any data project. In this guide we have gone through DataTypes as well as Case, Convert and Format as solutions.

Contact us if you want to find out more or discuss references from our clients.

Find out about our Business Intelligence Consultancy Service.

Need help handling dates in SQL Server or Power BI?

Explore our How-To Guides for real-world fixes

Learn key terms in SQL Glossary

Browse our Data Integration & Automation posts for setup tips

Blog Posted by David Laws

LinkedIn