Thanks to the diversity of cultures on Earth, we have a variety of date formats. For numeric dates, we have month-day-year, day-month-year, and year-month-day. We also have short and long formats. Dates can be mixed with time, which is another story. This reality follows us at work. That’s why the SQL date format is not something we can take easy.
In the Philippines, we use 2 formats: month-day-year and day-month-year. Month-day-year is the general format for numeric dates. But with longer date formats, we interchangeably use day-month-year and month-day-year.
At work, I never encountered SQL Server date format setting other than month-day-year. However, it varies in reports and files for data exchanges and Extract-Transform-Load (ETL) project. Let alone users who change it in their stations for personal preferences! With all these scenarios, not dealing with them appropriately is a recipe to break.
Do your apps deal with different cultures? That’s another level of complexity and a problem when dealing with these various SQL date formats. In this article, we’ll explore the standard to deal with all these varieties and examples. Read on until the end! But before we proceed, let’s take a short detour on how SQL Server stores dates.
How SQL Server Stores Dates
Take a guess. Does SQL Server store 03/29/2021 21:35 as is in the database? How about 2021-03-29? Are they stored as formatted strings? Basing on this documentation from Microsoft, it is not like this.
Let’s take the DATE data type, for example. It is stored as a 3-byte integer.
How do we know?
Well, Microsoft says it is so but does not provide more information. It doesn’t mean we can’t know it for sure. First, the DATE data type minimum value is 01/01/0001 or January 1, 1 CE or Common Era. To convert this to an integer, we convert that date to VARBINARY first, like this:
SELECT CAST(CAST('01/01/0001' AS DATE) AS VARBINARY(3))The result is 0x000000 in hexadecimal format. From this value, we can see that the integer value of January 1, 1 CE is 0. It is logical because that’s the minimum DATE value.
Now, we advance 1 day.
SELECT CAST(CAST('01/02/0001' AS DATE) AS VARBINARY(3)) -- January 2, 1 CEThe result is 0x010000. This is a little tricky, but this post gave us an idea. We cannot treat it as we see it. The bytes are reversed, and the actual hex value is 0x000001. If you know a little about hex numbers, you know that this is equal to 1 – it’s 1 day from the starting point, January 1, 1 CE.
Now, let’s try a recent date: 03/29/2021.
SELECT CAST(CAST('03/29/2021' AS DATE) AS VARBINARY(3))The result is 0x55420B. When we reverse it, it becomes 0x0B4255. This time, we can’t know the value by looking at it. So, we multiply it by 1 as an integer.
SELECT 0x0B4255 * CAST(1 AS INT)The result is 737,877. This is the number of days from January 1, 1 CE. Let’s verify it with DATEDIFF.
SELECT DATEDIFF(DAY,CAST('01/01/0001' AS DATE),CAST('03/29/2021' AS DATE))The result is the same: 737,877 days. Very cool!
Bottom-line: The Formatted Date is For Presentation Purposes Only
That’s how SQL Server stores DATE data types. It’s different for DATETIME, SMALLDATETIME, and DATETIME2, but still stored as integers. SQL Server calculates the time duration from the starting point and displays the date we can all understand.
Whether you’re looking at it in SQL Server Management Studio, dbForge Studio for SQL Server, or your app, 03/29/2021 is just a presentation. If you change the region or language, the stored value 0x55420B will remain the same.
Now we know that dates are not stored as strings. We can forget about storing dates in a specific format. That’s not how it works anyway. Instead, let’s look into different ways in SQL to format dates that your apps need.
The 4 Easy Ways to Format Dates
Let’s examine the following SQL date functions:
- CONVERT function
- SET LANGUAGE
- SET DATEFORMAT
- FORMAT function
You can also use an SQL query formatter.
1. CONVERT Function
CONVERT is one of the data conversion functions that can also serve for date formatting. Figure 1 displays an example.
The first two arguments of CONVERT are the target data type and the date value. The third one is optional but it also applies to dates. The numeric values are the SQL date format styles to use during conversion from date to string.
In Figure 1, Japan uses a year-month-day format with a slash as a separator. Germany uses day-month-year with dots as separators. Britain and France use the same sequence as Germany, but with a slash as a separator. Only the United States uses month-day-year with a hyphen as a separator.
In SQL, you can also convert the DATETIME expression to DATE.
For a complete list of CONVERT date format styles in SQL, check this reference from Microsoft.
2. SET LANGUAGE
This setting specifies the language applied to the session. It affects the date formats and system messages. When you set a language, you also implicitly apply the SET DATEFORMAT settings (we’ll work it out later).
For now, let’s check the examples in Figure 2. I am changing the language settings to Lithuanian, and then back to English.
Look at Figure 2. The Lithuanian date format is year-month-day. Whenever I try different dates, the long date always includes ‘m.’ before the month and ‘d.’ after the day. The first letter of the month and the name of the weekday are not capitalized either. It’s different for other language settings but you get the point.
For more information on SET LANGUAGE, check out this reference from Microsoft.
3. SET DATEFORMAT
This setting places the order of the month, day, and year for interpreting date character strings. It will override the implicit date format settings done by SET LANGUAGE. Here’s an example in Figure 3.
In Figure 3, the code uses DMY or day-month-year format. Under these settings, any date value set to a date variable should follow this pattern. 30/03/2021 matches this format, but 03/31/2021 triggers an error because 31 is not a valid month.
Thus, changes in the date format may break your app if the logic operates on the premise of another format.
For more information on SET DATEFORMAT, check out this reference from Microsoft.
4. FORMAT Function
Of all formatting options available, this one is the most flexible. It is similar for date formatting in .Net, as FORMAT relies on the presence of the .Net Framework on the server where SQL Server is installed. That’s the drawback of this option, however.
Just like doing it in C#, FORMAT takes a date value and a format string. Let’s have a few examples in Figure 4.
Like in .Net, in SQL Server, you can format dates using different separators. Also, you can position the month, day, and year anywhere. Then, you can get the cultural information and use its date format.
For more information and examples on FORMAT, check out this reference from Microsoft.
Now, we’ve identified 4 ways to format dates and the variety of date formats. Is there a standard format that always works when converting strings to dates?
ISO 8601 – The Most Portable, Error-Free SQL Date Format You Can Use for Conversion
ISO 8601 has been there since 1988. It is the international standard in the exchanges of data relating to dates and times.
It is also available in the CONVERT function as one of the date format styles: styles 126 and 127 are ISO 8601 compliant.
What makes it portable and error-free?
The Problem with Non-ISO 8601 Formats
Let’s demonstrate the trouble for non-ISO 8601 with an example:
DECLARE @d VARCHAR(10) = '03/09/2021';
SET LANGUAGE Italian;
SELECT FORMAT(CONVERT(DATETIME, @d),'D')
SET LANGUAGE English
SELECT FORMAT(CONVERT(DATETIME, @d),'D')
SET DATEFORMAT DMY
SELECT FORMAT(CONVERT(DATETIME, @d),'D')
SET DATEFORMAT MDY
SELECT FORMAT(CONVERT(DATETIME, @d),'D')
Depending on your date locale, you can interpret @d as March 9, 2021, or September 3, 2021. You can’t be sure which is which. That’s where the problem lies.
Check the result in Figure 5 below:
Even SQL Server doesn’t know it for sure!
Worst, this kind of scenario can break your app just like what happened in Figure 3 earlier. This situation is problematic for apps dealing with multicultural users.
Can ISO 8601 help with this?
Using ISO 8601 to Deal with Formatting Problems
Notice when the ISO 8601 format yyyyMMdd is used instead of MM/dd/yyyy and check the result in Figure 6:
It will always be March 9, no matter which language and date format settings are used. This is great for data exchanges and system integrations. If your user has another date format in the station, it won’t matter either.
If you need to transform your dates to strings and back, use ISO 8601.
ISO 8601 in SQL Server comes in 2 flavors:
- YYYYMMDD is for dates only.
- YYYY-MM-DDTHH:MM:SS for a mix of date and time, where T is the delimiter between date and time.
Other Ways to Handle Date Formats from Apps to SQL Server
1. Use Date-Aware Controls in the App
When you ask a user to input dates in a form, do not let them use free text. Use date controls that allow choosing from valid values only.
2. Convert to a Different Format Only When Needed
Don’t keep transforming dates to strings and vice versa. Use the native Date or DateTime data types of the calling app. If you need to transform them for whatever reason, use ISO 8601.
3. Set the Date/Time, Time Zone, and Culture in your App Startup, if Applicable
If your app uses a fixed date format, and your users love to tweak the date formats, you can fix both on application startup. Explicitly set what your app needs. It will be much better if your network admin can lock these settings.
Takeaways
Feeling overwhelmed by the variety of SQL date formats? It’s understandable if you do, but remember, it’s not impossible to master. To further enhance your understanding and ease the process, we recommend reading our comprehensive guide on Varchar to Date Conversions in SQL Server.
Here is what we have covered:
- SQL dates are stored as integers. What we see with our eyes is already formatted based on SQL Server settings. No matter how many times we change language and date formats, the stored value will remain the same. It’s useless to think of storing dates in a specific format.
- There are 4 ways to format dates: CONVERT, SET LANGUAGE, SET DATEFORMAT, and FORMAT.
- If you need to transform dates to strings and vice versa, use ISO 8601 format.
- There are 3 other ways to deal with date formats:
- using date-aware controls in your app;
- transforming dates to strings only when needed;
- setting the required time zone, culture, server date/time at startup when applicable.
Do you think this will be useful to you and to others? Then, please share this article on your favorite social media platforms.
Tags: sql date, sql server Last modified: July 24, 2023
