Chapter 1: Introduction to Azure Synapse Date and Time Functions

When engaging with Azure Synapse, I frequently encounter various formats related to date and time. For those who may not work with this platform regularly, I hope this cheat sheet, encompassing the key commands, proves helpful.

Current Date and Time Retrieval

Let’s kick things off with a straightforward yet valuable command: retrieving the current date and time. This can be essential for comparisons in analytics or during technical data transformations. The command is:

SELECT CURRENT_TIMESTAMP

System Date and Time Functionality

This function provides the date and time from the server running the SQL instance. Although technical, it can be beneficial for monitoring or within metadata processes:

SELECT SYSDATETIME()

Extracting Date Parts

You can extract specific components from a date, such as the day, month, or year. For instance, if I wish to extract just the year, I can use:

SELECT YEAR('2007-04-21')

This command will yield 2007 as the output.

Calculating Date Differences

To find the difference between two dates, the DATEDIFF function is invaluable. If the first date is earlier than the second, the result will be negative:

SELECT DATEDIFF(MONTH, '2007-04-21', '2007-07-21')

Date Validation with ISDATE

The ISDATE function checks whether a given input is a valid date type. This is particularly useful for avoiding errors in data processing:

IF ISDATE('2009-05-12') = 1 PRINT 'VALID'

ELSE PRINT 'INVALID';

Data Conversion with CONVERT

One of the most frequently utilized functions for me is CONVERT. It’s essential when external sources display dates as strings, and you want them formatted as standardized datetime in Synapse. The parameter 103 refers to the British or French date format:

SELECT CONVERT(datetime, '25/04/2019 09:00', 103)

This will output: 2019-04-25T09:00:00.0000000.

Summary

I often find myself searching for SQL date formats because I forget the correct expressions. Therefore, I have compiled this cheat sheet for Synapse SQL, highlighting what I believe are the most common use cases. Please let me know if there’s anything I may have overlooked.

Sources and Further Reading

[1] Microsoft, Date and Time Data Types and Functions (Transact-SQL) (2021)

Chapter 2: Practical Video Resources

Here are some helpful video resources to deepen your understanding of date and time functions in Azure Synapse.

The first video titled "DP 900 — Data Core Concepts CheatSheet" provides a valuable overview of key concepts related to data management within Azure.

The second video, "Examples of Wildcard Pattern in Copy Activity of ADF," presents practical applications of wildcard patterns in Azure Data Factory, which can be beneficial for data transformation tasks.