SQL Date Format Explained with Examples

In this article, you will learn how to format date and time values using SQL Statements. There are more than 50 SQL Date Formats that can be used in different versions of SQL Server, In simple terms, SQL date formatting is like changing the way dates are shown in a database to make them easier to understand and use. Just like how you can write dates in different ways on paper (like 12 Jan 2023, January 12, 2023, or 12/01/2023), SQL lets you change how dates appear in a database.

MySQL, and other database management systems. Apart from SQL Date Formats, Wikipedia provides a lengthy list of SQL date formats used in different parts of the world. For example in the United States, a unique date format 'mm-dd-yyyy' is being used. Some nations, such as Korea, Iran, and China, write the year first and the day last (yyyy-mm-dd). (MIT)

This different Date interpretation varies between different countries. Suppose you have stored the '01-05-1979' date in your SQL database. SQL Server and MySQL have their specific Date formats. On retrieval of this date can communicate different meanings to different peoples across the globe.

Hence for a database programmer and a user, SQL Date Format is very much necessary to learn. In this article, we will try to enlist and outline the details of all SQL Date Formats used in SQL Server and MySQL with east and practical examples.

As you have already learned, various SQL Date Functions and SQL Time Functions articles about the different functions like FORMAT() and CONVERT() can be used to format Date in a specific way.

 

1. Types of MS SQL Server Date Formats

Here's a simple table that explains some of the most common SQL date formats:

• yyyyMMdd: This format is compact and commonly used in programming and data storage. It sorts dates in chronological order when viewed as strings.
• ddMMyyyy: This format is frequently used in parts of the world where the day is traditionally written before the month. It's more common in everyday use than in programming.
• yyyy-MM-dd: This format is ISO standard, widely used internationally in both programming and documentation. It's clear and easy to understand.
• MM/dd/yyyy: This format is commonly used in the United States. The month comes first, which can be a bit confusing for those used to seeing the day first.

 

2. Use of FORMAT() Function in SQL

Let's start explaining the use of FORMAT() Microsoft SQL Server Function for formatting the dates and times. Before MS SQL SERVER 2008 version, the CONVERT() Function was to perform all SQL Date Formats and to convert from one Date Format to other. We will also explain the use of CAST() and CONVERT() for SQL Date convert and format options in this article. After learning the syntax, you will be demonstrated a list of the available SQL examples on how to use the CONVERT() SQL Server function to handle different SQL SERVER Date formats in a database.

2.1 Syntax of FORMAT() Function

The FORMAT function in SQL is a powerful tool for formatting dates and times in a way that's easy to read and understand. It's particularly useful because it allows you to convert the standard format of date and time data into any format you desire.

The syntax of the SQL Server FORMAT function is the following:

FORMAT (value, format [, culture])

• value: This is the date or time you want to format.
• format: This is a string that defines the desired format.
• culture (optional): This specifies a culture, like 'en-US' for American English or 'de-DE' for German, to use the correct format for that culture.

 

2.2 Different types of supported date formats

Here is the table which shows the list of supported date format with different data types:

 

2.3 Some Examples using SQL FORMAT() Function

Example 1: Formatting a Date

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

Output: 2024-01-23 (assuming today's date is January 23, 2024)

Example 2: Formatting DateTime

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS FormattedDateTime;

Output: 2024-01-23 15:45:30 (if the current time is 3:45:30 PM)

Example 3: Formatting SmallDateTime

SELECT FORMAT(CAST('2024-01-23 15:45:30' AS smalldatetime), 'dd/MM/yyyy') AS FormattedSmallDateTime;

Output: 23/01/2024

Example 4: Formatting DateTime2

SELECT FORMAT(SYSDATETIME(), 'MMM dd, yyyy HH:mm:ss.fffffff') AS FormattedDateTime2;

Output: Jan 23, 2024 15:45:30.1234567

 

3. SQL Server Date FORMAT with Culture

The Culture is an optional argument that can be used for SQL Date convert and format. With the culture argument, we can get SQL Date Formats for different regions. As Format() function is very rich in syntax and arguments and can be used with all data types supported by .NET and SQL Server. Hence we have a lengthy list of culture codes to use with FORMAT SQL Date Format and Convert function. 

 

3.1 Syntax with Culture Code

The syntax to format a date with a specific culture in SQL Server is:

FORMAT (value, format [, culture])

Here, the culture parameter is an optional string that represents a culture, such as 'en-US' for American English or 'fr-FR' for French.

 

3.2 Examples of Date Formatting with Culture

Example 1: US English Format

SELECT FORMAT(GETDATE(), 'd', 'en-US') AS USFormat;

Output might be: 01/23/2024 (assuming today's date is January 23, 2024, in the US format)

Example 2: German Format

SELECT FORMAT(GETDATE(), 'd', 'de-DE') AS GermanFormat;

Output might be: 23.01.2024 (German format for the same date)

Example 3: British English Format

SELECT FORMAT(GETDATE(), 'd', 'en-GB') AS UKFormat;

Output might be: 23/01/2024 (UK format for the same date)

Example 4: Indian Format

SELECT FORMAT(GETDATE(), 'd', 'hi-IN') AS IndianFormat;

Output might be: 23-01-2024 (Indian format for the same date)

 

3.3 List of Some Common Culture Codes

• en-US: United States
• de-DE: Germany
• en-GB: United Kingdom
• fr-FR: France
• hi-IN: India
• zh-CN: China (Simplified Chinese)
• ru-RU: Russia
• ja-JP: Japan

 

4. Using the CONVERT Function for Date Formatting

The CONVERT function in SQL is another useful tool for formatting dates. Unlike the FORMAT function, which is more flexible and allows for custom format strings, CONVERT is used to convert a date to one of several predefined styles.

 

4.1 Syntax Explained

The basic syntax for the CONVERT function is:

CONVERT(data_type, value [, style])

• data_type: The target data type (like varchar).
• value: The date value you want to convert.
• style: An optional integer that specifies the format.

 

4.2 Examples

Let's look at examples using various predefined styles:

Example 1: Format as yyyy-mm-dd

SELECT CONVERT(varchar, GETDATE(), 23) AS FormattedDate;

Output might be: 2024-01-23 (if today's date is January 23, 2024)

Example 2: Format as hh:mm:ss

SELECT CONVERT(varchar, GETDATE(), 108) AS FormattedTime;

Output: 15:45:30 (if the current time is 3:45:30 PM)

Example 3: Format as mm-dd-yyyy hh:mm:ss.mmm

SELECT CONVERT(varchar, GETDATE(), 121) AS FullDateTime;

Output: 01-23-2024 15:45:30.123 (for January 23, 2024, at 3:45:30 PM with milliseconds)

Example 4: Format as Month DD YYYY

SELECT CONVERT(varchar, GETDATE(), 107) AS MonthDayYear;

Output: Jan 23, 2024

 

4.3 List of Some Common Styles for CONVERT

• Style 1: MM/DD/YY
• Style 2: YY.MM.DD
• Style 3: DD/MM/YY
• Style 4: DD.MM.YY
• Style 5: DD-MM-YY
• Style 10: MM-DD-YY
• Style 11: YY/MM/DD
• Style 12: YYMMDD
• Style 23: YYYY-MM-DD
• Style 101: MM/DD/YYYY
• Style 102: YYYY.MM.DD
• Style 103: DD/MM/YYYY
• Style 104: DD.MM.YYYY
• Style 105: DD-MM-YYYY
• Style 110: MM-DD-YYYY
• Style 111: YYYY/MM/DD
• Style 112: YYYYMMDD
• Style 120: YYYY-MM-DD HH:MI:SS (24h)
• Style 121: YYYY-MM-DD HH:MI:SS.MMM (24h)

 

5. Working with DATEDIFF and DATEADD Functions

The DATEDIFF and DATEADD functions in SQL are essential for manipulating and calculating differences in date and time values. They are particularly useful in scenarios where you need to analyze time-based data or perform date arithmetic.

 

5.1 DATEDIFF Function

The DATEDIFF function calculates the difference between two dates and returns the result in units specified by the user.

Syntax

DATEDIFF(interval, startdate, enddate)

• interval: The part of the date to calculate the difference (like day, month, year).
• startdate: The starting date.
• enddate: The ending date.

Example: Calculating the number of days between two dates:

SELECT DATEDIFF(day, '2024-01-01', '2024-01-23') AS DaysDifference;

Output: 22 (the number of days between January 1 and January 23, 2024).

5.2 DATEADD Function

The DATEADD function is used to add a specified number of time units to a date, returning a new date.

Syntax

DATEADD(interval, number, date)

• interval: The part of the date to add (like day, month, year).
• number: The number of units to add.
• date: The original date.

Example: Adding 30 days to a specific date:

SELECT DATEADD(day, 30, '2024-01-01') AS NewDate;

Output: 2024-01-31 (30 days added to January 1, 2024).

 

6. Handling Time Zones and Localization in SQL

6.1 Impact of Time Zones:

• Time zones affect how date and time data is interpreted and displayed.
• A time in one time zone can correspond to a completely different time in another.
• Handling time zones correctly is essential for global applications to ensure consistency and accuracy.

 

6.2 Setting Time Zones in SQL:

• In SQL, you can set the time zone at a session level or within a query.
• For databases like PostgreSQL, you use SET TIME ZONE to define the time zone for a session.

SET TIME ZONE 'America/New_York';

In MySQL, the CONVERT_TZ() function can convert a datetime value from one time zone to another.

SELECT CONVERT_TZ('2024-01-23 08:00:00', 'UTC', 'America/New_York') AS NewYorkTime;

 

6.3 Localization in Date Formatting

Impact of Localization:

• Different regions and cultures have various formats for displaying dates.
• Localization ensures that dates are presented in a familiar format to users, enhancing readability and understanding.

Catering to Different Language or Cultural Formats:

• SQL Server's FORMAT function allows for formatting dates according to different cultural norms.
• You can use culture-specific format strings to display dates in the preferred format of a particular region.

SELECT FORMAT(GETDATE(), 'd', 'en-US') AS USFormat; -- U.S. format (MM/dd/yyyy)
SELECT FORMAT(GETDATE(), 'd', 'de-DE') AS GermanFormat; -- German format (dd.MM.yyyy)

Example of Handling Localization:

• In an international application, you might need to display the date in the format preferred by the user's region.
• By using localization techniques, the same date can be formatted as 01/23/2024 in the U.S. (MM/dd/yyyy), 23.01.2024 in Germany (dd.MM.yyyy), and 2024/01/23 in Japan (yyyy/MM/dd).

 

7. Advanced Date Formatting Techniques

Advanced date formatting in SQL involves using specific functions to handle date and time values with greater precision and detail. Two key functions used for this purpose are EXTRACT and DATEPART.

 

7.1 EXTRACT Function

The EXTRACT function is primarily used in SQL databases like PostgreSQL to retrieve a specific part of a date or time value.

Syntax

EXTRACT(part FROM date)

• part: The part of the date or time value you want to extract (like year, month, day, hour).
• date: The date or time value from which to extract the part.

Example: Extracting the year from a date:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-01-23') AS Year;

Output: 2024

 

7.2 DATEPART Function

The DATEPART function is used in SQL Server to return a specific part of a date or time value.

Syntax

DATEPART(part, date)

• part: The part of the date or time value you want to extract.
• date: The date or time value from which to extract the part.

Example: Extracting the month from a date:

SELECT DATEPART(month, '2024-01-23') AS Month;

Output: 1 (for January)

 

8. Summary

In summary, SQL date formatting encompasses various functions and techniques to handle and manipulate date and time values effectively. The FORMAT function allows for flexible formatting of dates, including cultural localization. The CONVERT function offers a more structured approach with predefined styles. For calculating differences and manipulating dates, DATEDIFF and DATEADD are essential, while EXTRACT and DATEPART are used for precision handling and extracting specific parts of dates. These functions are crucial in data analysis, reporting, and ensuring the accurate representation of time-sensitive data in SQL-based applications.

For more detailed information and further reading, you can refer to the official documentation of these functions in various SQL databases:

• SQL Server FORMAT Function: SQL Server FORMAT() Function
• SQL Server CONVERT Function: SQL Server CONVERT() Function
• DATEDIFF and DATEADD Functions: DATEDIFF and DATEADD
• PostgreSQL EXTRACT Function: PostgreSQL EXTRACT
• SQL Server DATEPART Function: SQL Server DATEPART() Function