SELECT knowledge FROM sql_resources WHERE category='sql-server' AND slug='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;

Loading code...

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

Loading code...

date_1	smalldatetime	datetime_1	datetime2	datetimeoffset	time
1987-12-21	1987-12-21T13:12:00.000	1987-12-21T13:12:54.067	1987-12-21T13:12:54.065	1987-12-21T09:42:54.065Z	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;

Loading code...

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;

Loading code...

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

Loading code...

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

Loading code...

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

Loading code...

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');

Loading code...

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;

Loading code...

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

Loading code...

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

Loading code...

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

Loading code...

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;

Loading code...

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

Dates and Times

Introduction

The Basics

Data Types

Converting to date/time data types

Converting using CAST

Converting using CONVERT

Converting using PARSE

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

Formatting your dates

Comparing Dates

Comparing with operators

Dynamic comparisons

Adding/Subtracting Dates

Date Parts

Culture Parameters

Converting using `CAST`

Converting using `CONVERT`

Converting using `PARSE`