Dates In SQL Server: Managing & Manipulating Dates

2018-05-01

Working with dates correctly gives us a lot of power. Dates are a surprisingly complex part of databases, especially if we take into consideration the general complexities in calendars.

SQL Server has a lot of subtleties, and some formatting nuances which can be difficult to remember. This is the first in a short series of articles I’ll put together about working with dates in SQL Server. I’ll start by covering some fundamentals, and provide a few common examples for working with dates.

Date data types

It’s worth being familiar with the SQL Server data types for date & time. As we are looking at storing dates, I’ll focus on:

DATETIME2
SMALLDATETIME
DATE

DATE is more recent, it was new in 2014 (it’s not the best known addition). If you are working with whole days, DATE is the best option. It’s also what I’m going to focus on for my date/calendar articles.

If you need to include time (so not just the whole day) then use DATETIME2 or SMALLDATETIME (unless you need the backwards compatibility of DATETIME). I’m going to come back to these two in another post, SMALLDATETIME does rounding differently to DATETIME2 which can have unexpected results.

Formatting dates

A golden rule is to let a client application handle the presentation of the data. Data should be handed to the client with the required attributes and presentation handled there. Having said that, many of us run simple reports/data checking steps from within SSMS.

There are two ways of applying a little formatting of dates in SQL Server:

Converting to VARCHR and applying a format mask.
Using the FORMAT command.

Formatting using CONVERT, DateName & DatePart

CONVERT has been around for a lot longer and is best for backwards compatibility (if you need to support before 2008, then CAST will have to do). The basic idea is that the data is converted into a different data type (in our case a VARCHAR) with a formatting mask applied.

SELECT CONVERT(VARCHAR(<LENGTH>), GetDate(), <STYLE>)
   ...
;

In the above the <LENGHT> can be used to trim the output, a length of 10 can give yyyy-mm-dd where as 19 will provide yyyy-mm-dd hh:mi:ss. The <STYLE> picks the format for the date. The style numbers can be hard to remember (unlike using a format string). They also provide a finite number of outputs before people start using string replacement commands. The most important/common numbers to know are:

120: ODBC canonical
110: USA
103: British

DateName allows us to get human readable attributes from the date, like the name of the day of week. This can be very useful for some tasks (if we want to track/show activity which only happens on weekdays for example).

SELECT DateName(dw, GetDate()) AS TheDate_day_name,
;

The other way to get date information out is to use DatePart. This is useful if you want to get a format not supported by the style list. Or as a means to roll up/group dates by month/week.

SELECT DatePart(MONTH, GetDate())
   ...
;

Examples

SELECT GetDate(),
       DateName(dw, GetDate())             AS TheDate_day_name,
       CONVERT(VARCHAR, GetDate(), 103)    AS TheDate_UK_date,
       CONVERT(VARCHAR(10),GetDate(), 120) AS TheDate_ISO_date,
       DateName(m, GetDate())              AS TheDate_month_name,
       DatePart(m, GetDate())              AS TheDate_month_value,
       DatePart(wk, GetDate())             AS TheDate_week_of_year
;

See samples of all the above used in this SQL Fiddle.

Formatting using FORMAT

The FORMAT command came out in 2012, and allows for some handy shortcuts in presenting numbers and dates. It has a number of default natural geographic outputs as well as the opportunity to create any custom formatting required.

SELECT GetDate(),
       FORMAT(GetDate(), 'd', 'en-gb') AS [TheDate_UK_numeric],
       FORMAT(GetDate(), 'D', 'en-gb') AS [TheDate_UK_alphanumeric],
       FORMAT(GetDate(), 'd', 'en-us') AS [TheDate_US_numeric],
       FORMAT(GetDate(), 'D', 'en-us') AS [TheDate_US_alphanumeric],
       FORMAT(GetDate(), 'dd - yy_ MMM', 'en-gb')
                                     AS [TheDate_Custom_Format]
;

For a working example, see this SQL Fiddle.

More advanced date formats can be applied using the information for:

As I build out this collection of articles on dates, I realise that I know a lot more about dates in SQL Server than I realised there was to know, and there is more to them than I know. I was going to put more in this post, but it’s longer than I thought it would be already.