Dates and Times

Introduction

Date and time data in SQL Server allows doing a lot more than just storing a time of the day or a date. In fact, there is such a wide variety of functionalities that it may be hard sometimes to know what to use. This article contains some tips for the date and time functionality supported by SQL Server.

The Basics

Data Types

In t-SQL, dates and times can be one of the following datatypes:

DATETIME is the most flexible one, but has a precision of .000, .003, .007 seconds. However, if your application is to be implemented globally or needs to consider time differences, you should use DATETIMEOFFSET.

But first, you may want to know if a given data input is, in fact, a date. In this regard, the function ISDATE() returns a boolean of 1, if the argument is [a subpart of or a] DATETIME or a SMALLDATETIME, and 0 otherwise.

SELECT 
  ISDATE('1987-12-21') is_date_1,
  ISDATE('12:06PM') is_date_2,
  ISDATE('Hello World!') is_date_3;

Converting to date/time data types

Your data will not always be in one of these data types. However, you can use CAST, CONVERT, or PARSE to convert them into a date - time data type.

Converting using CAST

In order to convert your string to one of the date types, your string must be in one of the following formats:

SELECT
  CAST('1987-12-21' as DATE) date_1,
  CAST('1987-12-21 13:12' AS SMALLDATETIME) smalldatetime,
  CAST('1987-12-21 13:12:54.065' AS DATETIME) datetime_1,
  CAST('1987-12-21 13:12:54.065' as DATETIME2) datetime2,
  CAST('1987-12-21 13:12:54.065+03:30' as DATETIMEOFFSET) datetimeoffset,
  CAST('13:12:54' as TIME) time;

Converting using CONVERT

Using CONVERT is useful if you want to transform a string of chars into a date or time data type. To do so, you merely need to call the CONVERT function, and specify the target data type and the value within the argument.

SELECT
  CONVERT(DATETIME, '1987-12-21 06:07PM') date_convert;

Converting using PARSE

An alternative to convert a string of chars into a date or time data type is to using the PARSE function. In short, this function translates a given expression into the requested data type. Moreover, you can specify the target culture within this function to give it the format you need.

SELECT
  PARSE('1987-12-21 06:07PM' AS DATETIME2 USING 'Tr-TR') date_parse_1;

Can I convert my date / time to a unix timestamp?

In MySQL, there are several functions that allow you to get a date or time as a number from a given date (i.e. the difference). In Transact-SQL you can do this via the function DATEDIFF. The syntax is simple: you call DATEDIFF(unit, 'Benchmark_Date', Your_Date), where unit will be s, mi or n, h, d, wk, m, qq, or y, depending on whether you want the difference in seconds, minutes, hours, days, weeks, months, quarters, or years.

If you get an error, you can try DATEDIFF_BIG, which allows calculating differences in miliseconds (ms), for example.

Note that when the date of reference is later than the input, the difference is returned as a negative value.  

SELECT 
  DATEDIFF(n,  '1970-01-01 00:00:00', '1987-12-21') diff_minutes,
  DATEDIFF(wk, '1980-03-07 00:00:00', '1987-12-21') diff_weeks,
  DATEDIFF(qq, '2018-12-02 00:00:00', '1987-12-21') diff_quarters;

Formatting your dates

If you have a date but you want to give it a format specific to a given country or culture, you can use the FORMAT function. Its syntax is pretty easy: FORMAT(date, 'D'/'d', 'culture'), where 'D' yields the date in text and 'd' in number. You can also use 'yyyy/dd/mm' or some other variation to specify the format.

If you are curiours, you can find a cheatsheet with all of them here.

Note that the FORMAT function can be applied to other data types such as currency.

However, keep in mind that FORMAT returns the dates as strings of chars, and not DATEs.

SELECT
  FORMAT(CAST('1987-12-21' as DATE), 'D', 'de-DE') AS German_Date_Text,
  FORMAT(CAST('1987-12-21' as DATE), 'D', 'en-US') AS US_Date_Text,
  FORMAT(CAST('1987-12-21' as DATE), 'd', 'en-gb') AS GB_Date_Num,
  FORMAT(CAST('1987-12-21' as DATE), 'D', 'zh-cn') AS Chinese_Date_Text,
  FORMAT(CAST('1987-12-21' as DATE), 'D', 'hi-IN') AS Hindi_Date_Text,
  FORMAT(CAST('1987-12-21' as DATE), 'D', 'ru-RU') AS Russian_Date_Text,
  FORMAT(CAST('1987-12-21' as DATE), 'D', 'th-Th') AS Thai_Date_Text,
  FORMAT(CAST('1987-12-21' as DATE), 'yyyy.dd.mm') AS YYYYDDMM_Date_Dot;

But this is not all. If you want to know the weekday it was on a given date, or the day of the year, for example, you can use the function DATENAME. Using it is pretty simple: you merely have to call it, ask what is it you want to know (i.e. year, month, day, dayofyear, or weekday), and specify the date.

SELECT  
  DATENAME(year,      '08/03/1995') year,
  DATENAME(month,     '08/03/1995') month,
  DATENAME(day,       '08/03/1995') day,
  DATENAME(dayofyear, '08/03/1995') dayofyear,
  DATENAME(weekday,   '08/03/1995') weekday;

Comparing Dates

We usually need dates to make comparisons: we may need to filter some data, or compare a current value with one we had for the same item a few years ago. Luckily, SQL Server has many functionalities that allow this.

Comparing with operators

Date and time data types are stored as numeric values, so one of the easiest ways to compare dates is to use a comparison operator:

SELECT 
  date
FROM (
  SELECT CAST('2021-01-01' AS DATE) AS date
  UNION ALL (
    SELECT CAST('2021-01-15' AS DATE) AS date
  )
  UNION ALL (
    SELECT CAST('2021-02-01' AS DATE) AS date
  )
) AS dates
WHERE
  date BETWEEN '2021-01-01' AND '2021-01-31';

Alternatively, you can use >=, < to achieve the same result:

SELECT 
  date
FROM (
  SELECT CAST('2021-01-01' AS DATE) AS date
  UNION ALL (
    select CAST('2021-01-15' AS DATE) AS date
  )
  UNION ALL (
    SELECT CAST('2021-02-01' AS DATE) AS date
  )
) AS dates
WHERE
  (date >= '2021-01-01') AND (date < '2021-01-31');

To filter dates, you can use strings of chars in the format YYYY-MM-DD

Dynamic comparisons

What happens if we want our query to always pull the last n days? Well, in that case, and in order to avoid having to update manually our query everyday, we need dynamic comparisons.

We first need to get the current date, and the easiest way to do so in Transact SQL is to call the function CONVERT and use the target data type and SYSDATETIME() as arguments.

Note that SYSDATETIME and SYSUTCDATE have more precision than GETDATE and GETUTCDATE. The SYSDATETIMEOFFSET function includes the system time zone offset. You can assign SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET to a variable of any of the date and time types.

SELECT
  CONVERT(DATE, SYSDATETIME()) crrnt_date,
  CONVERT(DATETIMEOFFSET, SYSDATETIMEOFFSET()) crrnt_datetime_offset,
  CONVERT(TIME, SYSUTCDATETIME()) crrnt_UTC_time,
  CONVERT(DATETIME2, CURRENT_TIMESTAMP) crrnt_datetime2,
  CONVERT(DATETIME, GETDATE()) crrnt_get_date,
  CONVERT(DATE, GETUTCDATE()) crrnt_date_UTC;

Once we have the system's date or time, we can find the difference between the system value a target date using the function DATEDIFF:

SELECT 
  DATEDIFF(d, CONVERT(DATE, SYSDATETIME()), CAST('1987-12-21' as DATE)) diff_days;

It is also possible that we want to filter our data, let's say, for the last 12 months. In that case, we need to compare our date column with the current date and make sure the difference is 12 or fewer months.

SELECT
  date
FROM (
  SELECT CAST('2021-04-06' AS DATE) AS date
  UNION all (
    SELECT CAST('2021-06-05' AS DATE) AS date
  )
  UNION all (
    SELECT CAST('2020-04-04' AS DATE) AS date
  )
) AS dates
WHERE
  DATEDIFF(MONTH, date, CONVERT(DATE, SYSDATETIME())) <= 12

Finally, it is possible that we want to know the difference between two dates and exclude certain days. You may want to know the difference between the expected arrival date, and the actual arrival date, yet exclude Sundays when no deliveries take place. To do so, you can do the following:

SELECT
  DATEDIFF(DAY, est_delivery, actual_delivery) - DATEDIFF(WEEK, est_delivery, actual_delivery) as days_late
FROM
(
  SELECT
    CAST('2021-01-22' as DATE) as est_delivery,
    CAST('2021-01-25' as DATE) as actual_delivery
) as delivery_dates

Adding/Subtracting Dates

If you want to transform your data (e.g. adding a week to a date, or subtracting a month), you can use the function DATEADD. Its syntax is DATEADD (datepart, number, date), where datepart can be any of these.

Note that to subtract, you merely have to "add" a negative value.

Also, you can use DATEDIFF and SYSDATETIME to  set a countdown.

SELECT
  CAST ('2021/12/23 13:12:54' as DATETIME) original_date,
  DATEADD (week , 2 , '2021/12/23 13:12:54') date_plus_2wk,
  DATEADD (month , -1 , '2021/12/23 13:12:54') date_minus_1month, 
  DATEADD (hour , 7 , '2021/12/23 13:12:54') date_plus_7hour,
  DATEDIFF(d, '2021/12/23 13:12:54', sysdatetime()) countdown;

Date Parts

If you want to add or subtract a certain amount of days, years,... to a given date, you can specify so via the following "date parts". Note that these are the same as the ones you use to specify the units in your DATEDIFF.

Culture Parameters

You can use the list below as a cheatsheet to specify a given culture for your date and time when you use the function FORMAT(). However, if you omit the culture argument the output will be formatted according to the language of the current session.

If you want to see how to use any of the invariants below, you can find a couple of examples here.

Be careful when specifying the culture, because it is case sensitive.

Also, you can use the culture arguments below to specify the currency when you use money data types (you can find more about this here).