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.
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:
1
SELECT CAST(GETDATE() AS Date) as Date;
1
2024-06-11
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:
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:
1
FORMAT(date, format [, culture ])
In particular, it accepts:
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:
1
2024-06-11
The most important date components supported by the FORMAT
SQL Server date function are:
Component | Description | Value |
---|---|---|
y | Single-digit year for dates before or equal to 2000 and two-digit year for dates after 2000 | 24 |
yy | Two-digit year | 24 |
yyyy | Four-digit year | 2024 |
M | One or two-digit month (1-12) | 6 |
MM | Two-digit month (01-12) | 06 |
MMM | Abbreviated month name | Jun |
MMMM | Full month name | June |
d | One or two-digit day of month (1-31) | 11 |
dd | Two-digit day of month (01-31) | 11 |
ddd | Abbreviated month name | Tue |
dddd | Full day name | Tuesday |
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:
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:
Format | Result |
---|---|
MdY | 61124 |
M/dd/yyyy | 6/11/2024 |
M/dd/yy | 6/11/24 |
MM/dd/yyyy | 06/11/2024 |
MM/dd/yy | 06/11/24 |
M-dd-yyyy | 6-11-2024 |
M-dd-yy | 6-11-24 |
MM-dd-yyyy | 06-11-2024 |
MM-dd-yy | 06-11-24 |
M.dd.yyyy | 6.11.2024 |
M.dd.yy | 6.11.24 |
MM.dd.yyyy | 06.11.2024 |
MM.dd.yy | 06.11.24 |
MMM dd, yyyy | Jun 11, 2024 |
MMM dd yyyy | Jun 11 2024 |
MMM yyyy dd | Jun 2024 11 |
dd MMM, yyyy | 11 Jun, 2024 |
dd MMM yyyy | 11 Jun 2024 |
dd yyyy MMM | 11 2024 Jun |
yyyy MMM dd | 2024 Jun 11 |
yyyy dd MMM | 2024 11 Jun |
MM/yyyy/dd | 06/2024/11 |
MM-yy/dd | 06-24/11 |
MM.yyyy.dd | 06.2024.11 |
d/M/yyyy | 11/6/2024 |
d-M-yyyy | 11-6-2024 |
d.M.yyyy | 11.6.2024 |
dd/MM/yyyy | 11/06/2024 |
dd-MM-yyyy | 11-06-2024 |
dd.MM.yyyy | 11.06.2024 |
dd/yy/MM | 11/24/06 |
dd-yy-MM | 11-24-06 |
dd.yy.MM | 11.24.06 |
yy/MM/dd | 24/06/11 |
yyyy/MM/dd | 2024/06/11 |
yy-MM-dd | 24-06-11 |
yyyy-MM-dd | 2024-06-11 |
yyyyMMdd | 20240611 |
You can verify that with the following SQL Server date format query:
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
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:
Learn more in the official documentation.
SQL Server FORMAT Date Function Best Practices
Here are some best practices you need to know:
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:
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: