Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in MS SQL Server

Introduction
Dealing with dates
Working with time data
Date and time date
Extracting dates and times
Doing arithmetic with dates
Converting date and time data
35. The AT TIME ZONE function
Building date and time data from parts
Summary and review

Instruction

Excellent! Now let's move on to the AT TIME ZONE function. It allows you to convert from the stored date/time to the date/time of a specified time zone. Simply place the date you want to convert before AT TIME ZONE and then write the name of the desired time zone afterwards, like this:

SELECT
  LaunchedDatetime,
  LaunchedDatetime AT TIME ZONE 'Pacific Standard Time' AS LaunchedDatetimePST
FROM Aircraft
WHERE Id = 1;

Don't forget to enclose the new time zone name in single quotes!

The original LaunchedDatetime value is '2014-06-10 07:55:00.0000000 +01:00'. The new value, represented in Pacific Standard Time, is '2014-06-09 23:55:00.0000000 -07:00'.

Exercise

Let's convert the DiscontinuationDatetime times for all discontinued aircraft into 'Central European Standard Time'. Name the column DiscontinuationDatetimeCET.

Stuck? Here's a hint!

Use:

column AT TIME ZONE nameOfTimeZone

Remember that all the discontinued aircraft do not have NULL in DiscontinuationDatetime column.