DATE
SQL SERVER

The Ultimate Guide to the SQL Server Date Format

intro

Let's learn everything you need to know about SQL Server Date format operations, from accepted date formats to the use of the FORMAT function.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
SQL Server
SQL Server is a database management system by Microsoft

Dealing with dates is always a headache because the same date string might have different meanings in different countries. For example, “06/11/2024” is June 11 in the US and November 6 in Italy. This is why it’s so critical to rely on a standard SQL Server date format.

In this guide, you will see standard date formats, explore the date components supported by T-SQL, and understand how to use the FORMAT SQL Server date function.

Let’s dive in!

What Is the Format of the DATE Type in SQL Server?

The default SQL Server DATE format is yyyy-MM-dd, as you can verify with the following query:

Copy
        
1 SELECT CAST(GETDATE() AS Date) as Date;

This calls the GETDATE() function to retrieve the current date and time, and then uses an SQL cast to convert the result to DATE.

The end result of the query will be something like:

Copy
        
1 2024-06-11
Note the result of the query executed in DbVisualizer
Note the result of the query executed in DbVisualizer

As you can see, the returned date follows the yyyy-MM-dd format.

At the same time, when casting a string to the DATE data type, other string literal formats are supported by T-SQL. These include:

  • yyyyMMdd (e.g., 20240611)
  • MMM dd yyyy (e.g., Jun 11 2024)
  • yyyy/MM/dd (e.g., 2024/06/11)
  • MM/dd/yyyy (e.g., 06/11/2024)
  • yyyy.MM.dd (e.g., 2024.06.11)
  • MM.dd.yyyy (e.g., 06.11.2024)

Depending on the SQL Server settings and the configured OS language, some other regional formats might be accepted.

Note: While SQL Server accepts various date formats, sticking to the standard yyyy-MM-dd format ensures portability and reduces errors.

For more information, explore our guide on the SQL date date types.

SQL Server Date Format List

To better understand the possible SQL server date formats, you first need to take a look at the allowed date components by FORMAT(). This popular date function transforms DATE values into formatted string literals and has the following syntax:

Copy
        
1 FORMAT(date, format [, culture ])

In particular, it accepts:

  • date: A DATE value.
  • format: A string containing the target output format for the input date.
  • culture: An optional string for specifying a culture (e.g., 'en-US', es-MX, etc.). If this argument is not provided, the language of the current session is used.

Keep in mind that the FORMAT function is nondeterministic and works only if the .NET Common Language Runtime (CLR) component is installed. In case of an invalid format string, it returns NULL.

Now, suppose you are dealing with the following SQL Server DATE value in the standard format:

Copy
        
1 2024-06-11

The most important date components supported by the FORMAT SQL Server date function are:

ComponentDescriptionValue
ySingle-digit year for dates before or equal to 2000 and two-digit year for dates after 200024
yyTwo-digit year24
yyyyFour-digit year2024
MOne or two-digit month (1-12)6
MMTwo-digit month (01-12)06
MMMAbbreviated month nameJun
MMMMFull month nameJune
dOne or two-digit day of month (1-31)11
ddTwo-digit day of month (01-31)11
dddAbbreviated month nameTue
ddddFull day nameTuesday

It should now be clear why the date Jun 11 2024 corresponds to 2024-06-11 in the standard SQL Server date format yyyy-MM-dd.

Date Formatting in SQL Server

Now, we will see how to use the FORMAT function to convert SQL Server DATE values into formatted string date literals.

Again, suppose you are dealing with the following date:

Copy
        
1 2024-06-11

Below is a summary table with the results of the FORMAT function applied by popular date formats recommended by the SQL server documentation:

FormatResult
MdY61124
M/dd/yyyy6/11/2024
M/dd/yy6/11/24
MM/dd/yyyy06/11/2024
MM/dd/yy06/11/24
M-dd-yyyy6-11-2024
M-dd-yy6-11-24
MM-dd-yyyy06-11-2024
MM-dd-yy06-11-24
M.dd.yyyy6.11.2024
M.dd.yy6.11.24
MM.dd.yyyy06.11.2024
MM.dd.yy06.11.24
MMM dd, yyyyJun 11, 2024
MMM dd yyyyJun 11 2024
MMM yyyy ddJun 2024 11
dd MMM, yyyy11 Jun, 2024
dd MMM yyyy11 Jun 2024
dd yyyy MMM11 2024 Jun
yyyy MMM dd2024 Jun 11
yyyy dd MMM2024 11 Jun
MM/yyyy/dd06/2024/11
MM-yy/dd06-24/11
MM.yyyy.dd06.2024.11
d/M/yyyy11/6/2024
d-M-yyyy11-6-2024
d.M.yyyy11.6.2024
dd/MM/yyyy11/06/2024
dd-MM-yyyy11-06-2024
dd.MM.yyyy11.06.2024
dd/yy/MM11/24/06
dd-yy-MM11-24-06
dd.yy.MM11.24.06
yy/MM/dd24/06/11
yyyy/MM/dd2024/06/11
yy-MM-dd24-06-11
yyyy-MM-dd2024-06-11
yyyyMMdd20240611

You can verify that with the following SQL Server date format query:

Copy
        
1 SELECT 2 FORMAT(CAST('2024-06-11' AS DATE), 'Mdy') AS 'Mdy', -- 61124 3 FORMAT(CAST('2024-06-11' AS DATE), 'M/dd/yyyy') AS 'M/dd/yyyy', -- 6/11/2024 4 FORMAT(CAST('2024-06-11' AS DATE), 'M/dd/yy') AS 'M/dd/yy', -- 6/11/24 5 FORMAT(CAST('2024-06-11' AS DATE), 'MM/dd/yyyy') AS 'MM/dd/yyyy', -- 06/11/2024 6 FORMAT(CAST('2024-06-11' AS DATE), 'MM/dd/yy') AS 'MM/dd/yy', -- 06/11/24 7 FORMAT(CAST('2024-06-11' AS DATE), 'M-dd-yyyy') AS 'M-dd-yyyy', -- 6-11-2024 8 FORMAT(CAST('2024-06-11' AS DATE), 'M-dd-yy') AS 'M-dd-yy', -- 6-11-24 9 FORMAT(CAST('2024-06-11' AS DATE), 'MM-dd-yyyy') AS 'MM-dd-yyyy', -- 06-11-2024 10 FORMAT(CAST('2024-06-11' AS DATE), 'MM-dd-yy') AS 'MM-dd-yy', -- 06-11-24 11 FORMAT(CAST('2024-06-11' AS DATE), 'M.dd.yyyy') AS 'M.dd.yyyy', -- 6.11.2024 12 FORMAT(CAST('2024-06-11' AS DATE), 'M.dd.yy') AS 'M.dd.yy', -- 6.11.24 13 FORMAT(CAST('2024-06-11' AS DATE), 'MM.dd.yyyy') AS 'MM.dd.yyyy', -- 06.11.2024 14 FORMAT(CAST('2024-06-11' AS DATE), 'MM.dd.yy') AS 'MM.dd.yy', -- 06.11.24 15 FORMAT(CAST('2024-06-11' AS DATE), 'MMM dd, yyyy') AS 'MMM dd, yyyy', -- Jun 11, 2024 16 FORMAT(CAST('2024-06-11' AS DATE), 'MMM dd yyyy') AS 'MMM dd yyyy', -- Jun 11 2024 17 FORMAT(CAST('2024-06-11' AS DATE), 'MMM yyyy dd') AS 'MMM yyyy dd', -- Jun 2024 11 18 FORMAT(CAST('2024-06-11' AS DATE), 'dd MMM, yyyy') AS 'dd MMM, yyyy', -- 11 Jun, 2024 19 FORMAT(CAST('2024-06-11' AS DATE), 'dd MMM yyyy') AS 'dd MMM yyyy', -- 11 Jun 2024 20 FORMAT(CAST('2024-06-11' AS DATE), 'dd yyyy MMM') AS 'dd yyyy MMM', -- 11 2024 Jun 21 FORMAT(CAST('2024-06-11' AS DATE), 'yyyy MMM dd') AS 'yyyy MMM dd', -- 2024 Jun 11 22 FORMAT(CAST('2024-06-11' AS DATE), 'yyyy dd MMM') AS 'yyyy dd MMM', -- 2024 11 Jun 23 FORMAT(CAST('2024-06-11' AS DATE), 'MM/yyyy/dd') AS 'MM/yyyy/dd', -- 06/2024/11 24 FORMAT(CAST('2024-06-11' AS DATE), 'MM-yy/dd') AS 'MM-yy/dd', -- 06-24/11 25 FORMAT(CAST('2024-06-11' AS DATE), 'MM.yyyy.dd') AS 'MM.yyyy.dd', -- 06.2024.11 26 FORMAT(CAST('2024-06-11' AS DATE), 'd/M/yyyy') AS 'd/M/yyyy', -- 11/6/2024 27 FORMAT(CAST('2024-06-11' AS DATE), 'd-M-yyyy') AS 'd-M-yyyy', -- 11-6-2024 28 FORMAT(CAST('2024-06-11' AS DATE), 'd.M.yyyy') AS 'd.M.yyyy', -- 11.6.2024 29 FORMAT(CAST('2024-06-11' AS DATE), 'dd/MM/yyyy') AS 'dd/MM/yyyy', -- 11/06/2024 30 FORMAT(CAST('2024-06-11' AS DATE), 'dd-MM-yyyy') AS 'dd-MM-yyyy', -- 11-06-2024 31 FORMAT(CAST('2024-06-11' AS DATE), 'dd.MM.yyyy') AS 'dd.MM.yyyy', -- 11.06.2024 32 FORMAT(CAST('2024-06-11' AS DATE), 'dd/yy/MM') AS 'dd/yy/MM', -- 11/24/06 33 FORMAT(CAST('2024-06-11' AS DATE), 'dd-yy-MM') AS 'dd-yy-MM', -- 11-24-06 34 FORMAT(CAST('2024-06-11' AS DATE), 'dd.yy.MM') AS 'dd.yy.MM', -- 11.24.06 35 FORMAT(CAST('2024-06-11' AS DATE), 'yy/MM/dd') AS 'yy/MM/dd', -- 24/06/11 36 FORMAT(CAST('2024-06-11' AS DATE), 'yyyy/MM/dd') AS 'yyyy/MM/dd', -- 2024/06/11 37 FORMAT(CAST('2024-06-11' AS DATE), 'yy-MM-dd') AS 'yy-MM-dd', -- 24-06-11 38 FORMAT(CAST('2024-06-11' AS DATE), 'yyyy-MM-dd') AS 'yyyy-MM-dd', -- 2024-06-11 39 FORMAT(CAST('2024-06-11' AS DATE), 'yyyyMMdd') AS 'yyyyMMdd'; -- 2024061
Executing the SQL Server date format query in DbVisualizer
Executing the SQL Server date format query in DbVisualizer

Executing the SQL Server date format query in DbVisualizer

With a powerful SQL Server database client like DbVisualizer, you can even customize the display format for date, time, and timestamp values through a dedicated configuration option:

Untitled
Customize the display format

Learn more in the official documentation.

SQL Server FORMAT Date Function Best Practices

Here are some best practices you need to know:

  • Do not forget that the FORMAT function accepts a DATE value and not a string, so be sure to cast the string literal to DATE before passing it to the function.
  • Prefer using standard date formats such as yyyy-MM-dd to ensure consistency and avoid ambiguity.
  • Do not forget that the FORMAT function is locale-sensitive, meaning it formats dates based on the server's locale settings. Be explicit about output formats and culture if locale-specific formatting is not desired.
  • Use clear and explicit format strings to avoid confusion. For example, prefer yyyy-MM-dd over formats like yy-MM-dd to limit ambiguity.
  • When possible, use the ISO 8601 format for date and time literals, as it is unambiguous and widely accepted.

Conclusion

In this guide, you learned more about formatting DATE in SQL Server. You now know what the standard date format for SQL Server is, how to change it using the FORMAT function, and what the best practices are when it comes to date formatting in T-SQL.

Dealing with date data types is not always easy, but with DbVisualizer it becomes a breeze! As a full-featured database client, it supports all data types for more than 50 database technologies. This is just one of the many features supported by this powerful tool, which also includes advanced query optimization features and automatic generation of ERD-type schemas. Try DbVisualizer for free today!

FAQ

How does the SQL Server date FORMAT function work?

The FORMAT SQL Server date function accepts a date expression and formats it as a string in the specified format. This function supports various output date formats and comes with localization capabilities.

What is the most common date format for SQL Server?

The most common date format for SQL Server is the ISO 8601 format, YYYY-MM-DD (e.g., 2024-06-11). That is unambiguous, widely accepted, and consistent across different systems and regional settings, making it the preferred choice for storing and exchanging date-related data in SQL Server.

Does the SQL Server date format comply with the ANSI SQL standard definition?

Yes, the SQL Server date format complies with the ANSI SQL standard definition for the Gregorian calendar:

NOTE 85 - Datetime data types will allow dates in the Gregorian format to be stored in the date range 0001-01-01 CE through 9999-12-31 CE.

What happens when we’re using dates in an unsupported format in SQL Server?

SQL Server raises the following error:

Copy
        
1 [Code: 241, SQL State: 22007] Conversion failed when converting date and/or time from character string.

What are the main aspects to keep in mind when formatting DATE in SQL Server?

When formatting DATE in SQL Server, you should:

  • Prefer using YYYY-MM-DD for clarity and compatibility.
  • Be aware of locale-specific formatting effects.
  • Use the FORMAT function judiciously as it may impact performance.
  • Use explicit and unambiguous output format strings.
  • Validate formats across environments for consistent behavior.
Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

The Definitive Guide to the SQL Server GETDATE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-10-31
title

CONCAT_WS SQL Server Function: Ultimate Guide With Examples

author Antonello Zanini tags SQL SERVER 6 min 2024-10-24
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

SQL Server Date Diff: Calculating the Difference Between Two Dates

author Antonello Zanini tags SQL SERVER 6 min 2024-10-07
title

A Complete Guide to the SQL Server FOR XML PATH Clause

author Antonello Zanini tags SQL SERVER XML 8 min 2024-10-01
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

A Complete Guide to the SQL Server COALESCE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-09-23
title

SQL DATEPART: Get a Part of a Date in SQL Server

author Antonello Zanini tags SQL SERVER 8 min 2024-09-19
title

The Definitive Guide to the NULL SQL Server Value

author Antonello Zanini tags SQL SERVER 7 min 2024-09-12

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗