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.
DATETIMEOFFSET allow you to set the precision of fractional seconds. For example:
'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:
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.