Format SQL Server Date

Welcome, Dev! In this article, we will discuss how to format SQL Server date using different date formats. SQL Server provides a variety of date and time formats, which can be used to display dates in different formats according to the requirement. Let’s explore the different date formats provided by SQL Server.

Understanding Date Formats in SQL Server

Before we dive into the different ways to format date in SQL Server, it’s important to understand how SQL Server stores dates. SQL Server stores dates as two-part values, one part is the date and the other part is the time. The date is stored as a number of days since January 1, 1900 (for dates between January 1, 1900, and December 31, 2049), while the time is stored as the number of ticks since midnight.

The date and time value can be retrieved using the GETDATE() function in SQL Server.

Example:

SELECT GETDATE() AS [Current Date and Time];

Output:

Current Date and Time
2021-05-21 14:20:21.970

The above query will return the current date and time in SQL Server.

Formatting Date in SQL Server

Date Formats:

SQL Server provides different date formats, which can be used to convert the date into any format. Some common date formats are:

Date Formats:

Date Format	Output
YYYY-MM-DD	2021-05-21
DD/MM/YYYY	21/05/2021
MMM DD, YYYY	May 21, 2021
DD/MM/YYYY HH:MI:SS	21/05/2021 14:20:21

Let’s explore each date format in detail.

YYYY-MM-DD Format:

The YYYY-MM-DD format is one of the most widely used date formats in SQL Server. This format is also known as ISO 8601 format.

To convert the date in YYYY-MM-DD format, use the CONVERT function with style code 120.

Syntax:

SELECT CONVERT(varchar(10), GETDATE(), 120) AS [YYYY-MM-DD];

Output:

Output:

YYYY-MM-DD
2021-05-21

The above query will return the current date in YYYY-MM-DD format.

DD/MM/YYYY Format:

The DD/MM/YYYY format is another commonly used date format in SQL Server.

To convert the date in DD/MM/YYYY format, use the CONVERT function with style code 103.

Syntax:

SELECT CONVERT(varchar(10), GETDATE(), 103) AS [DD/MM/YYYY];

Output:

Output:

DD/MM/YYYY
21/05/2021

The above query will return the current date in DD/MM/YYYY format.

MMM DD, YYYY Format:

The MMM DD, YYYY format is widely used in the USA.

To convert the date in MMM DD, YYYY format, use the CONVERT function with style code 106.

Syntax:

SELECT CONVERT(varchar(20), GETDATE(), 106) AS [MMM DD, YYYY];

Output:

Output:

MMM DD, YYYY
May 21, 2021

The above query will return the current date in MMM DD, YYYY format.

DD/MM/YYYY HH:MI:SS Format:

The DD/MM/YYYY HH:MI:SS format is used to display the date and time in SQL Server.

To convert the date and time in DD/MM/YYYY HH:MI:SS format, use the CONVERT function with style code 105.

Syntax:

SELECT CONVERT(varchar(20), GETDATE(), 105) + ' ' + CONVERT(varchar(20), GETDATE(), 108) AS [DD/MM/YYYY HH:MI:SS];

Output:

Output:

DD/MM/YYYY HH:MI:SS
21/05/2021 14:20:21

The above query will return the current date and time in DD/MM/YYYY HH:MI:SS format.

FAQs:

Q: What is the default date format in SQL Server?

A: The default date format in SQL Server is YYYY-MM-DD.

Q: How to convert datetime to date in SQL Server?

A: To convert datetime to date in SQL Server, use the CAST or CONVERT function with style code 101.

Syntax:

SELECT CAST(GETDATE() AS date) AS [Date];

Output:

Output:

Date
2021-05-21

The above query will return the date from the datetime value in SQL Server.

Q: How to convert varchar to date in SQL Server?

A: To convert varchar to date in SQL Server, use the CONVERT function with style code 120.

Syntax:

SELECT CONVERT(date, '2021-05-21', 120) AS [Date];

Output:

Output:

Date
2021-05-21

The above query will convert varchar to date in SQL Server.

Conclusion:

In this article, we have learned about different date formats provided by SQL Server. We have discussed how to convert the date in different formats using the CONVERT function in SQL Server. We hope this article will help you to understand the different date formats in SQL Server.