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.
| 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.
- GETDATE(): Returns the current date and time of the system on which the SQL Server instance is running.
- SYSDATETIME(): Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
- 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.
- 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.
- 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, 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.
| Culture | Language |
|---|---|
| en-gb | English (United Kingdom) |
| en-US | English (United States) |
| de-de | German |
| fr | French |
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.
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 Posted by David Laws
