Select Distinct Logo Clear Background

Business Analytics Blog

SQL Server Date Handling

date handling in SQL Server

Handling dates 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.

 

Data type Format Range Accuracy Storage size (bytes) User-defined fractional second precision Time zone offset
time hh:mm:ss [.nnnnnnn] 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds 3 to 5 Yes No
date YYYY-MM-DD 0001-01-01 through 9999-12-31 1 day 3 No No
smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 through 2079-06-06 1 minute 4 No No
datetime YYYY-MM-DD hh:mm:ss [.nnn] 1753-01-01 through 9999-12-31 0.00333 second 8 No No
datetime2 YYYY-MM-DD hh:mm:ss [.nnnnnnn] 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds 6 to 8 Yes No
datetimeoffset YYYY-MM-DD hh:mm:ss [.nnnnnnn] [+ -]hh:mm 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 nanoseconds 8 to 10 Yes

The output of the original data types are below:

Datetime Smalldatetime
2024-02-05 14:02:19.040 2024-02-05 14:02:00

The “new” data types are below.

Datetime2 Date Time Datetimeoffset
2024-02-05 14:02:19.0400000 2024-02-05 14:02:19.0400000 2024-02-05 14:02:19.0400000 +00:00

 

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.

GETDATE SYSDATETIME
2024-01-31 10:18:51.487 2024-01-31 10:18:51.4898231

 

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

 

SYSDATETIMEOFFSET SYSUTCDATETIME CURRENT_TIMESTAMP
2024-01-31 10:18:51.4898231 +00:00 2024-01-31 10:18:51.4898231 2024-01-31 10:18:51.487

 

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

 

Statement Length Output
1 CONVERT(VARCHAR, GETDATE(), 1) 02/05/24
2 CONVERT(VARCHAR, GETDATE(), 2) 24.02.05
3 CONVERT(VARCHAR, GETDATE(), 3) 05/02/24
4 CONVERT(VARCHAR, GETDATE(), 4) 05.02.24
5 CONVERT(VARCHAR, GETDATE(), 5) 05-02-24
6 CONVERT(VARCHAR, GETDATE(), 6) 05 Feb 24
7 CONVERT(VARCHAR, GETDATE(), 7) Feb 05, 24
8 CONVERT(VARCHAR, GETDATE(), 8) 12:51:38
9 CONVERT(VARCHAR, GETDATE(), 9) Feb 5 2024 12:51:38:430PM
10 CONVERT(VARCHAR, GETDATE(), 10) 02-05-24
11 CONVERT(VARCHAR, GETDATE(), 11) 24/02/05
12 CONVERT(VARCHAR, GETDATE(), 12) 240205
13 CONVERT(VARCHAR, GETDATE(), 13) 05 Feb 2024 12:51:38:433
14 CONVERT(VARCHAR, GETDATE(), 14) 12:51:38:433
15 CONVERT(VARCHAR, GETDATE(), 15) 2024-02-05 12:51:38
16 CONVERT(VARCHAR, GETDATE(), 16) 2024-02-05
17 CONVERT(VARCHAR, GETDATE(), 17) 2024-02-05 12:51:38
18 CONVERT(VARCHAR, GETDATE(), 18) 2024-02-05
19 CONVERT(VARCHAR, GETDATE(), 19) 12:51:38
20 CONVERT(VARCHAR, GETDATE(), 20) 2024-02-05 12:51:38
21 CONVERT(VARCHAR, GETDATE(), 21) 2024-02-05 12:51:38.433
22 CONVERT(VARCHAR, GETDATE(), 22) 02/05/24 12:51:38 PM
23 CONVERT(VARCHAR, GETDATE(), 23) 2024-02-05
24 CONVERT(VARCHAR, GETDATE(), 24) 12:51:38
25 CONVERT(VARCHAR, GETDATE(), 25) 2024-02-05 12:51:38.433
26 CONVERT(VARCHAR, GETDATE(), 26) 2024-05-02 12:51:38.433
27 CONVERT(VARCHAR, GETDATE(), 27) 02-05-2024 12:51:38.433
28 CONVERT(VARCHAR, GETDATE(), 28) 02-2024-05 12:51:38.433
29 CONVERT(VARCHAR, GETDATE(), 29) 05-02-2024 12:51:38.433
30 CONVERT(VARCHAR, GETDATE(), 30) 05-2024-02 12:51:38.433
31 CONVERT(VARCHAR, GETDATE(), 31) 2024-05-02
32 CONVERT(VARCHAR, GETDATE(), 32) 02-05-2024
33 CONVERT(VARCHAR, GETDATE(), 33) 02-2024-05
34 CONVERT(VARCHAR, GETDATE(), 34) 05-02-2024
35 CONVERT(VARCHAR, GETDATE(), 35) 05-2024-02
100 CONVERT(VARCHAR, GETDATE(), 100) Feb 5 2024 12:51 PM
101 CONVERT(VARCHAR, GETDATE(), 101) 02/05/2024
102 CONVERT(VARCHAR, GETDATE(), 102) 2024.02.05
103 CONVERT(VARCHAR, GETDATE(), 103) 05/02/2024
104 CONVERT(VARCHAR, GETDATE(), 104) 05.02.2024
105 CONVERT(VARCHAR, GETDATE(), 105) 05-02-2024
106 CONVERT(VARCHAR, GETDATE(), 106) 05 Feb 2024
107 CONVERT(VARCHAR, GETDATE(), 107) Feb 05, 2024
108 CONVERT(VARCHAR, GETDATE(), 108) 12:51:38
109 CONVERT(VARCHAR, GETDATE(), 109) Feb 5 2024 12:51:38:437 PM
110 CONVERT(VARCHAR, GETDATE(), 110) 02-05-2024
111 CONVERT(VARCHAR, GETDATE(), 111) 2024/02/05
112 CONVERT(VARCHAR, GETDATE(), 112) 20240205
113 CONVERT(VARCHAR, GETDATE(), 113) 05 Feb 2024 12:51:38:440
114 CONVERT(VARCHAR, GETDATE(), 114) 12:51:38:440
115 CONVERT(VARCHAR, GETDATE(), 115) 12:51:38
120 CONVERT(VARCHAR, GETDATE(), 120) 2024-02-05 12:52:04
121 CONVERT(VARCHAR, GETDATE(), 121) 2024-02-05 12:52:04.753
126 CONVERT(VARCHAR, GETDATE(), 126) 2024-02-05T12:52:04.753
127 CONVERT(VARCHAR, GETDATE(), 127) 2024-02-05T12:52:04.753
130 CONVERT(VARCHAR, GETDATE(), 130) 26 ??? 1445 12:52:04:753 PM
131 CONVERT(VARCHAR, GETDATE(), 131) 26/07/1445 12:52:04:753 PM

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, formatculture)

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.

Culture Language
en-gb English (United Kingdom)
en-US English (United States)
de-de German
fr French

 

Conclusion

Dates are 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.

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Blog

Blog Posted by David Laws

 

David Laws Principal Consultant

LinkedIn