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.

Loading code...
is_date_1is_date_2is_date_3
110

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
Loading code...
date_1smalldatetimedatetime_1datetime2datetimeoffsettime
1987-12-211987-12-21T13:12:00.0001987-12-21T13:12:54.0671987-12-21T13:12:54.0651987-12-21T09:42:54.065Z13: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.

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.  

Loading code...
diff_minutesdiff_weeksdiff_quarters
9450720407-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.

Loading code...
German_Date_TextUS_Date_TextGB_Date_NumChinese_Date_TextHindi_Date_TextRussian_Date_TextThai_Date_TextYYYYDDMM_Date_Dot
Montag, 21. Dezember 1987Monday, December 21, 198721/12/19871987年12月21日21 दिसम्बर 198721 декабря 1987 г.21 ธันวาคม 25301987.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.

Loading code...
yearmonthdaydayofyearweekday
1995August3215Thursday

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
Loading code...
date
2021-01-01
2021-01-15

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

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.

Loading code...
crrnt_datecrrnt_datetime_offsetcrrnt_UTC_timecrrnt_datetime2crrnt_get_datecrrnt_date_UTC
2021-10-182021-10-18T12:32:25.273Z12:32:25.2722021-10-18T12:32:25.2802021-10-18T12:32:25.2802021-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:

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.

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:

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.

Loading code...
original_datedate_plus_2wkdate_minus_1monthdate_plus_7hourcountdown
2021-12-23T13:12:54.0002022-01-06T13:12:54.0002021-11-23T13:12:54.0002021-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'