How to Format Dates in Azure Synapse – Over 1800 Examples Included

You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

This blog will brilliantly break down and explain everything about the formatting dates in any way you desire on an Azure Synapse or SQL Server system. I will include over 1800 examples to avoid confusion about formatting dates.

Azure Synapse and SQL Server use two ways to format dates: the CONVERT and FORMAT functions to transform raw dates as you want them presented.

Here is a picture to show you an example of all forms and their results, just in case you need to see the format quickly.

Most people don’t know that you can use the FORMAT command in the WHERE clause to eliminate specific rows from returning. The Azure Synapse query example below uses FORMAT in the WHERE and AND clauses to only return rows where the customer hit the website on a Saturday in December when the time was ’07:54:34′ and the milliseconds = 000.

SQL Server and Azure Synapse allow formatting dates with different locales involving the FORMAT() function. The general structure is as follows:

FORMAT(GETDATE(), format_string, locale)

GETDATE(): Retrieves the current date and time.

format_string: Specifies the desired format for the date. It can include placeholders for components like year, month, day, etc.

Locale: Indicates the cultural setting or locale where the date should be formatted. It is provided as a string identifier, such as ‘en-US’ or ‘en-gb,’ representing English in the United States or the United Kingdom, respectively. This syntax allows for flexibility in displaying dates according to the conventions of different cultures, making it useful for applications with diverse user bases.

Here is a table with different locale syntax and the result.

The Azure Synapse CONVERT statement converts an expression from one data type to another. In the provided examples, you will see that CONVERT is applied to format the current date and time obtained through GETDATE(), which optionally can be a date column, into a varchar data type with different styles or numbering.

The numbering in the CONVERT statement represents different styles for date and time formatting. Each number corresponds to a specific predefined format. Here is a summary of some of the styles you will see in the table following this explanation:

0: Default or system style.

1: U.S. standard style (mm/dd/yy).

2: SQL Server style (yy.mm.dd).

3: British/French style (dd/mm/yy).

4: German style (dd.mm.yy).

5: Italian style (dd-mm-yy).

6: Standard style with century (yyyy/mm/dd).

7: U.S. style with century (mm/dd/yyyy).

8: Short date (e.g., ’20/07/12′).

9: Defined by the regional settings.

10: U.S. style with century (mm-dd-yyyy).

The numbers 100 and above are used for various date and time formats without a century (e.g., 101, 102, 103).

For example:

CONVERT(VARCHAR, GETDATE(), 1): Represents the U.S. standard date style.

CONVERT(VARCHAR, GETDATE(), 101): Represents the U.S. style with century.

These styles offer flexibility in formatting dates according to different conventions and preferences. You can choose the appropriate style based on the desired output format.

Here is a table showing you an example of the syntax and the result.

The Azure Synapse FORMAT statement is used to format date and time values in a specified format. In the provided examples, FORMAT is applied to format the current date and time obtained through GETDATE() into various date and time representations. Here is a summary of the format options used in your examples:

‘yyyy’: Year with four digits.

‘yy’: Year with two digits.

‘M’: Month as a single digit.

‘MM’: Month as two digits.

‘MMM’: Abbreviated month name.

‘MMMM’: Full month name.

‘d’: Day of the month as a single digit.

‘dd’: Day of the month as two digits.

‘ddd’: Abbreviated day of the week.

‘dddd’: Full day of the week.

‘h’: Hour in 12-hour clock format as a single digit.

‘hh’: Hour in 12-hour clock format as two digits.

‘H’: Hour in 24-hour clock format as a single digit.

‘HH’: Hour in 24-hour clock format as two digits.

‘m’: Minute as a single digit.

‘mm’: Minute as two digits.

‘s’: Second as a single digit.

‘ss’: Second as two digits.

‘tt’: AM or PM designation.

‘f’, ‘ff’, ‘fff’, ‘ffff’, ‘fffff’, ‘ffffff’: Fractional seconds.

Various date separator options, such as ‘-‘, ‘/’, ‘:’, and ‘ ‘ (space), are also used.

These format options provide flexibility to tailor the display of date and time values according to specific requirements and preferences.

Below is a table of the format option syntax and common short and long-date formats.

The table below shows you 1800 Azure Synapse date format examples.