Deborah's Developer MindScape

One of the challenges of working with data is working with dates.

Three common date data types are:

• Date
• DateTime
• DataTimeOffset

The first two are well known. The third one is newer and not yet widely used. For completeness, all three are detailed below.

Date is just that, a date with no time component.

· Format: YYYY-MM-DD

· Example: 1992-08-20

· Use it whenever you need a date without the time, such as a birth date.

DateTime is the date with a time.

· Format: YYYY-MM-DD hh:mm:ss[.nnn]

· Example: 2014-02-25 11:30:00

· Use it whenever you need a date with the time, such as an appointment date.

DateTimeOffset was new in SQL Server 2008. It represents a date and time with time zone awareness.

· Format: YYYY-MM-DD hh:mm:ss[.nnn] [+|-] hh:mm

· Example: 2014-02-25 11:28:22 -08:00

The -08 represents the time zone offset from UTC (Coordinated Universal Time). I’m on Pacific Standard Time right now, so this is my UTC.

· Use it whenever you need a date and time with knowledge of its time zone, such as a purchase date.

If you ever need to track transactions or other activities across time zones, the DateTimeOffset is the perfect choice.

For example, say a pair of woolen socks was purchased at 10AM in New York (10:00:00 -05:00). Sunscreen was purchased at 9 AM in San Francisco (9:00:00 -08:00). If the orders are processed in the sequence in which they were received, the New York order must be processed first.

Since the dates are time zone aware, operations such as sorting and less than / greater than will correctly handle the dates. No need to convert the dates to UTC.

Enjoy!