Good. Did you notice the format? Let's analyze it:
2014-06-10 07:55:00.125 +02:00
The first part is the date, and the second part is the time, down to fractions of a second. The final '+02:00'
refers to the time zone. In this case (Central European Summer Time), it means that we are two hours ahead of UTC (Coordinated Universal Time).
The actual date and time format depends on the data type selected for that column. The LaunchedDatetime
column uses the DATETIMEOFFSET
data type, but there are several other options that SQL Server provides:
SMALLDATETIME
takes fewer bytes, but does not include the time zone or fractional seconds. Its range is from 1900-01-01 00:00:00 to 2079-06-06 23:59:59.
DATETIME
takes more bytes and also does not allow for time zone information. It does allow up to three fractional seconds. Its range is from 1753-01-01 00:00:00 through 9999-12-31 23:59:59.997.
DATETIME2
is similar to DATETIME
, but it is newer and allows more fractional seconds and a wider range of date values. This is the one that we recommend for most use cases. And, it's the one that we'll be using most in this course.
DATETIMEOFFSET
is much like DATETIME2
, but it does include time zone information and takes a few more bytes.
Both DATETIME2
and DATETIMEOFFSET
allow you to set the precision of fractional seconds. For example:
DATETIME2(4)
: '2018-07-07 10:20:23.2432'
DATETIMEOFFSET(6)
: '2018-07-07 10:20:23.243277 +02:00'
The default precision for these data types is 7 digits.
You can compare the values in two DATETIMEOFFSET
columns like this:
SELECT
Id
FROM Aircraft
WHERE DiscontinuationDatetime < LaunchedDatetime;
The above query could be used to check for typos in our table; we can make sure there is no aircraft that was removed from service before it was even launched.