Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Dealing with dates
Working with time data
Date and time date
17. Date and time data types
Extracting dates and times
Doing arithmetic with dates
Converting date and time data
Building date and time data from parts
Summary and review

Instruction

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.

Exercise

Run the example query, and find out for yourself if there are errors in the Aircraft table.