Course Notes: Time Series Analysis in SQL Server

Course Notes

Use this workspace to take notes, store sample queries, and build your own interactive cheat sheet!

You will need to connect your SQL cells to an integration to run a query.

• You can use a sample integration from the dropdown menu. This includes the Course Databases integration, which contains tables you used in our SQL courses.
• You can connect your own integration by following the instructions provided here.

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes here about the concepts you've learned and SQL cells with code you want to keep.

Add your notes here

The CAST() function doesn't give us much flexibility in how we can ingest dates but it is the ANSI standard. Next, we will look at a T-SQL specific function, CONVERT().

The CONVERT() function behaves similarly to CAST(). When translating strings to dates, the two functions do exactly the same work under the covers.

SET LANGUAGE 'GERMAN'

SELECT d.DateText AS String, -- Convert to DATE CONVERT(DATE, d.DateText) AS StringAsDate, -- Convert to DATETIME2(7) CONVERT(DATETIME2(7), d.DateText) AS StringAsDateTime2 FROM dbo.Dates d;

PARSE() function to parse a string as a date type using a specific locale.

You might have noticed that the row with the string 19970614 was removed from #Dates. It turns out that the PARSE() function has some difficulty with strings in YYYYMMDD format and will not consistently parse them correctly.

SELECT d.DateText AS String, -- Parse as DATE using German Parse(d.DateText AS DATE USING 'de-de') AS StringAsDate, -- Parse as DATETIME2(7) using German Parse(d.DateText AS DATETIME2(7) USING 'de-de') AS StringAsDateTime2 FROM dbo.Dates d;

WORKING WITH OFFSETS

DATETIME2 have no in-built concept of time zone DATEITMEOFFSET type is made up three components: a date, a time and a upc offset SQL Server will then display the output string with all three components separated by spaces

CHANGING OFFSETS DECLARE @SomeDate DATETIMEOFFSET = '2019-04-10 12:59:02.3908505 -04:00';

SELECT SWITCHOFFSET(@SomeDate, '-07:00') AS LATime;

The SWITHCOFFSET() function allows us to change the time zone of a given input string.

CONVERTING TO DATETIMEOFFSET

DECLARE @SomeDate DATEITME2(3)='2019-04-10 12:59:02.390';

SELECT TODATETIMEOFFSET(@SomeDate, '-04:00') AS EDT;

• IT TAKES TO PARAM: AN INPUT DATE AND A TIME ZONE;

SELECT TZI.NAME, TZI.CURRENT_UTC_OFFSET, TZI.IS_CURRENTLY_DST FROM SYS.TIME_ZONE_INFO TZI WHERE TZI.NAME LIKE '%TIME ZONE%';

e can use the SWITCHOFFSET() function to change the time zone of a DATETIME, DATETIME2, or DATETIMEOFFSET typed date or a valid date string. SWITCHOFFSET() takes two parameters: the date or string as input and the time zone offset.

Fortunately, we have a Dynamic Management View (DMV) available to help us: sys.time_zone_info

In addition to SWITCHOFFSET(), we can use the TODATETIMEOFFSET() to turn an existing date into a date type with an offset.

Safe Functions TRY_CAST() TRY_CONVERT() TRY_PARSE()

UNSAFE FUNCTIONS - INVALID DATES BY CONVERTING THEM TO NULL PARSE() USES THE COMMON LANGUAGE RUNTIME UNDER THE COVERS, REACHING OUT CAST() ARE OPTIMIZED FUNCTIONS WHICH STAY INSIDE THE SQL SERVER DATABASE ENGINE CONFINES

DECLARE @GoodDateINTL NVARCHAR(30) = '2019-03-01 18:23:27.920', @GoodDateDE NVARCHAR(30) = '13.4.2019', @GoodDateUS NVARCHAR(30) = '4/13/2019', @BadDate NVARCHAR(30) = N'SOME BAD DATE';

-- The prior solution using TRY_CAST SELECT TRY_CAST(@GoodDateINTL AS DATETIME2(3)) AS GoodDateINTL, TRY_CAST(@GoodDateDE AS DATE) AS GoodDateDE, TRY_CAST(@GoodDateUS AS DATE) AS GoodDateUS, TRY_CAST(@BadDate AS DATETIME2(3)) AS BadDate;

SELECT TRY_PARSE(@GoodDateINTL AS DATETIME2(3)) AS GoodDateINTL, -- Fill in the correct region based on our input -- Be sure to match these data types with the -- TRY_CAST() examples above! TRY_PARSE(@GoodDateDE AS DATE USING 'de-de') AS GoodDateDE, TRY_PARSE(@GoodDateUS AS DATE USING 'en-us') AS GoodDateUS, -- TRY_PARSE can't fix completely invalid dates TRY_PARSE(@BadDate AS DATETIME2(3) USING 'sk-sk') AS BadDate;