SQL Resources/SQL Server/Dates and Times

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:

  • DATE: calendar date (e.g. 2021-10-06)
  • TIME: a time, just like in your watch (e.g. 17:23:11)
  • SMALLDATETIME: calendar date and time with a precision of 1 minute (e.g. 2021-10-06 17:23:11)
  • DATETIME: calendar date and time with a precision of 0.003 second (e.g. 2021-10-06 17:23:11.007)
  • DATETIME2: exactly like datetime, but with more precision (e.g. 2021-10-06 17:23:11.013)
  • DATETIMEOFFSET: this one behaves like DATETIME2 but can include timezone (e.g. 2021-10-06 17:23:11.013-05:00)

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;
is_date_1
1
is_date_2
1
is_date_3
0

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:

  • DATE: YYYY-MM-DD, although t-sql supports the formats myd, mdy, dmy, dym, ymd. The year can be specified with two or four digits. Both slash marks /, hyphens -, or periods . can be used to separate each part.
  • SMALLDATETIME: YYYY-MM-DD HH:MM
  • DATETIME: YYYY-MM-DD HH:MM:SS:FFF
  • DATETIME2: YYYY-MM-DD HH:MM:SS:FFF
  • DATETIMEOFFSET: YYYY-MM-DD HH:MM:SS:FFF [+|-]HH:MM
  • TIME: HH:MM:SS
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;
date_1
1987-12-21
smalldatetime
1987-12-21T13:12:00.000
datetime_1
1987-12-21T13:12:54.067
datetime2
1987-12-21T13:12:54.065
datetimeoffset
1987-12-21T09:42:54.065Z
time
13:12:54.000

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;
date_convert
1987-12-21T18:07:00.000

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;
date_parse_1
1987-12-21T18:07:00.000

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;
diff_minutes
9450720
diff_weeks
407
diff_quarters
-124

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; 
German_Date_Text
Montag, 21. Dezember 1987
US_Date_Text
Monday, December 21, 1987
GB_Date_Num
21/12/1987
Chinese_Date_Text
1987年12月21日
Hindi_Date_Text
21 दिसम्बर 1987
Russian_Date_Text
21 декабря 1987 г.
Thai_Date_Text
21 ธันวาคม 2530
YYYYDDMM_Date_Dot
1987.21.00

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;
year
1995
month
August
day
3
dayofyear
215
weekday
Thursday

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:

  • <, <=, >, =, != or <>, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN
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';
date
2021-01-01
2021-01-15

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');
date
2021-01-01
2021-01-15

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;
crrnt_date
2021-10-18
crrnt_datetime_offset
2021-10-18T12:32:25.273Z
crrnt_UTC_time
12:32:25.272
crrnt_datetime2
2021-10-18T12:32:25.280
crrnt_get_date
2021-10-18T12:32:25.280
crrnt_date_UTC
2021-10-18

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;
diff_days
-12355

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
date
2021-04-06
2021-06-05

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
days_late
2

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;
original_date
2021-12-23T13:12:54.000
date_plus_2wk
2022-01-06T13:12:54.000
date_minus_1month
2021-11-23T13:12:54.000
date_plus_7hour
2021-12-23T20:12:54.000
countdown
-66

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.

  • year yy, yyyy
  • quarter qq, q
  • month mm, m
  • dayofyear dy, y
  • day dd, d
  • week wk, ww
  • weekday dw, w
  • hour hh
  • minute mi, n
  • second ss, s
  • millisecond ms
  • microsecond mcs
  • nanosecond ns

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).

  • English 'en-US'
  • German 'de-DE'
  • French 'fr-FR'
  • Japanese 'ja-JP'
  • Danish 'da-DK'
  • Spanish 'es-ES'
  • Italian 'it-IT'
  • Dutch 'nl-NL'
  • Norwegian 'nn-NO'
  • Portuguese 'pt-PT'
  • Finnish 'fi-FI'
  • Swedish 'sv-SE'
  • Czech 'Cs-CZ'
  • Hungarian 'Hu-HU'
  • Polish 'Pl-PL'
  • Romanian 'Ro-RO'
  • Croatian 'hr-HR'
  • Slovak 'Sk-SK'
  • Slovenian 'Sl-SI'
  • Greek 'El-GR'
  • Bulgarian 'bg-BG'
  • Russian 'Ru-RU'
  • Turkish 'Tr-TR'
  • British English 'en-GB'
  • Estonian 'Et-EE'
  • Latvian 'lv-LV'
  • Lithuanian 'lt-LT'
  • Brazilian 'pt-BR'
  • Traditional Chinese 'zh-TW'
  • Korean 'Ko-KR'
  • Simplified Chinese 'zh-CN'
  • Arabic 'ar-SA'
  • Thai 'Th-TH'
Share SQL code
Write SQL with your team in real-time.
Share SQL code
Write SQL with your team in real-time.