SQL Date Format: How to Handle It the Smart Way

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 lightly.

In the Philippines, we use two 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 settings other than month-day-year. However, the details may vary in reports and files that require integration and data transformation. Moreover, some users change the date format for personal preferences! With all these scenarios, not dealing with dates appropriately is a recipe for disaster.

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. But before we proceed, let’s take a short detour on how SQL Server stores dates and what the date and time data types are in SQL Server.

Understanding SQL Server date and time data types

Before we dive deeper, it’s essential to understand what SQL Server actually gives us to work with. SQL Server supports several date and time data types, and each behaves differently when it comes to storage, precision, and formatting. Choosing the right one is the first step in avoiding headaches later.

DATE vs DATETIME vs DATETIME2: Key differences

• DATE
  
  • Stores only the date part (year, month, day).
  • Range: 0001-01-01 through 9999-12-31.
  • Storage: 3 bytes.
  • Perfect for scenarios where you don’t care about the time (birthdays, deadlines, or reporting periods).
    
• DATETIME
  
  • Stores both date and time (down to ~3.33 ms).
  • Range: 1753-01-01 through 9999-12-31.
  • Storage: 8 bytes.
  • One of the most common types historically, but limited in precision.
    
• DATETIME2
  
  • A modern replacement for DATETIME.
  • Higher precision (up to 100 ns) and a wider date range (0001-01-01 through 9999-12-31).
  • Storage: 6–8 bytes, depending on precision.
  • Recommended for new development where accuracy matters.

Generally, the best approach would be to use DATE for simple dates, DATETIME for legacy systems, DATETIME2 for everything else.

When to use DATETIMEOFFSET for time zone-aware storage

Storing dates is one thing, but what about storing time zones? That’s where DATETIMEOFFSET comes in, helping with:

• Storing the date, time, and the offset from UTC (e.g., 2021-03-29 10:00:00 -05:00).
• Serving global users, in apps, where “10 AM” in one location isn’t the same in another.

If your system integrates with APIs, scheduling apps, or multinational services, DATETIMEOFFSET ensures you don’t lose context when data crosses regions.

Precision and storage size differences between date types

There are significant differences between precision and storage size for date types in SQL Server, so it’s important to explore them to understand how they are used. Check the table below to explore the distinctions.

Data type	Storage (bytes)	Precision	Use case
DATE	3	Day only	Simple dates
SMALLDATETIME	4	Minutes (no seconds)	Lightweight logs, schedules
DATETIME	8	~3.33 ms	Legacy apps
DATETIME2	6–8	Up to 100 ns	High-precision timestamps
DATETIMEOFFSET	8–10	Up to 100 ns + time zone	Global apps, cross-time-zone data

Understanding these differences helps you pick the right balance between accuracy and performance.

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? Based 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 it does not provide more information on it. 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 CE

The 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!

What about DATETIMEOFFSET?

Now let’s bring DATETIMEOFFSET into the picture. Unlike DATE or DATETIME, this type doesn’t just store the date and time. It also includes the time zone offset from UTC.

For example, if you store:

SELECT CAST('2021-03-29 21:35:00 -05:00' AS DATETIMEOFFSET)

SQL Server doesn’t keep it as plain text. Internally, it splits the value into two parts:

• the UTC-based date and time (like how DATETIME2 would store it)
• the offset value (in minutes from UTC)

The storage size depends on the precision you choose, but it’s typically 8-10 bytes.

What’s interesting here is that no matter what offset you give, SQL Server can always calculate the absolute UTC time behind it. So 2021-03-29 21:35:00 -05:00 and 2021-03-30 02:35:00 +00:00 point to the exact same moment, just expressed differently.

Try this out:

DECLARE @dt DATETIMEOFFSET = '2021-03-29 21:35:00 -05:00';
SELECT SWITCHOFFSET(@dt, '+00:00');

The result will adjust the time while aligning it with the UTC offset. 

That’s the power of DATETIMEOFFSET: it keeps the original local time and knows how to convert it consistently across regions.

Handling time zones and offsets in SQL Server

When you’re working in a single office, storing dates and times in DATE or DATETIME2 might be enough. But as soon as your application spans multiple countries, things get tricky. “9 AM” in Manila isn’t the same as “9 AM” in London. That’s where SQL Server’s DATETIMEOFFSET type comes into play.

This data type stores three things together:

• the date
• the time with fractional seconds
• the time zone offset from UTC

For example:

DECLARE @dt DATETIMEOFFSET = '2021-03-29 09:00:00 -05:00';
SELECT @dt;

This value means March 29, 2021, 9:00 AM in a location that is five hours behind UTC. Internally, SQL Server keeps both the exact moment in time and the offset, so the context is never lost.

Converting between UTC and local time

DATETIMEOFFSET becomes really powerful when you need to switch between time zones. Two built-in functions help with that:

• SWITCHOFFSET() – changes the time zone offset but preserves the point in time.
  TODATETIMEOFFSET() – applies a time zone offset to a DATETIME2 value.

Example with SWITCHOFFSET:

DECLARE @dt DATETIMEOFFSET = '2021-03-29 09:00:00 -05:00';
SELECT SWITCHOFFSET(@dt, '+00:00') AS UtcTime;

Result: 2021-03-29 14:00:00 +00:00

Basically, it’s the same moment in time, but expressed in UTC.

And with TODATETIMEOFFSET:

DECLARE @dt2 DATETIME2 = '2021-03-29 14:00:00';
SELECT TODATETIMEOFFSET(@dt2, '+01:00') AS WithOffset;

Result: 2021-03-29 14:00:00 +01:00

Now it’s the same DATETIME2 value that is tied to a specific time zone offset.

This flexibility makes it much easier to normalize times to UTC for storage, then display them in a user’s local time.

Best practices for time zone-aware applications

Working with time zones can quickly get messy, so here are a few guidelines that help keep things under control:

• Store in UTC, display in local time.
  Save all timestamps in UTC using DATETIMEOFFSET or by normalizing to +00:00. Convert only when presenting data to the user.
• Don’t rely on system locale settings.
  Keep conversions explicit in your SQL code or application logic. Otherwise, the results may change depending on the server configuration.
• Use DATETIMEOFFSET for integrations.
  If your app exchanges data with APIs or partners in other regions, always use DATETIMEOFFSET to avoid ambiguity.
• Avoid manual calculations.
  Let SQL Server functions handle the math. Adding or subtracting offsets yourself is error-prone, especially with daylight saving rules.

By combining UTC storage with controlled conversions, you’ll ensure your data makes sense everywhere, whether your users are in Manila, London, or New York.

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 that 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.

But the real power of CONVERT is in its style codes. The first two arguments of CONVERT are the target data type and the date value. The third one is optional, but when you supply it, you can choose from dozens of format styles.

Here are some useful international ones:

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS US_MDY;       -- 03/29/2021
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS UK_DMY;       -- 29/03/2021
SELECT CONVERT(VARCHAR, GETDATE(), 104) AS German_DMY;   -- 29.03.2021
SELECT CONVERT(VARCHAR, GETDATE(), 105) AS Italian_DMY;  -- 29-03-2021
SELECT CONVERT(VARCHAR, GETDATE(), 106) AS Long;         -- 29 Mar 2021
SELECT CONVERT(VARCHAR, GETDATE(), 107) AS US_Long;      -- Mar 29, 2021
SELECT CONVERT(VARCHAR, GETDATE(), 108) AS TimeOnly;     -- 21:35:00
SELECT CONVERT(VARCHAR, GETDATE(), 110) AS USA;          -- 03-29-2021
SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Japan_YMD;    -- 2021/03/29
SELECT CONVERT(VARCHAR, GETDATE(), 112) AS ISO_Basic;    -- 20210329

As you can see, the same value from GETDATE() can be displayed in multiple culturally appropriate formats depending on the style number.

Note: CONVERT works with DATE, DATETIME, and DATETIME2, but not with the TIME data type alone. For times, you’ll need CAST or string functions. Also, formatting options are fixed to predefined styles, so you can’t create custom patterns.

For a complete list of CONVERT date format styles in SQL, check this reference from Microsoft.

CONVERT function style codes for date formatting

One of the reasons CONVERT is so widely used in SQL Server is its rich catalog of style codes. By passing a third argument to CONVERT, you can instantly change the output of a date or datetime expression into more than a hundred predefined formats. These styles cover U.S., European, and Asian formats, as well as long-form and numeric-only variations.

The trick is knowing which codes to use for the output you need.

Common CONVERT date styles (quick reference table)

Here are some of the most commonly used styles:

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS US;        -- 03/09/2021
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS British;   -- 09/03/2021
SELECT CONVERT(VARCHAR, GETDATE(), 104) AS German;    -- 09.03.2021
SELECT CONVERT(VARCHAR, GETDATE(), 105) AS Italian;   -- 09-03-2021
SELECT CONVERT(VARCHAR, GETDATE(), 110) AS USA;       -- 03-09-2021
SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Japan;     -- 2021/03/09
SELECT CONVERT(VARCHAR, GETDATE(), 112) AS ISO_Basic; -- 20210309
SELECT CONVERT(VARCHAR, GETDATE(), 113) AS Europe;    -- 09 Mar 2021 14:25:10:123
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS ODBC;      -- 2021-03-09 14:25:10

This quick reference shows just how diverse the outputs can be, from fully numeric styles like 112 to long formats with month names like 113.

Using CONVERT for custom format requirements

The main limitation of CONVERT is that you can’t design your own pattern. You must pick from the available style codes. However, by combining CONVERT with string manipulation functions (LEFT, RIGHT, SUBSTRING), you can sometimes approximate custom formats without resorting to FORMAT.

For example:

-- Extract just YYYY-MM from a datetime
SELECT LEFT(CONVERT(VARCHAR, GETDATE(), 23), 7) AS YearMonth;

This approach keeps the speed benefits of CONVERT while giving you some control over output.

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.

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 instance, you can apply localization directly with the third parameter:

-- Extract just YYYY-MM from a datetime
SELECT LEFT(CONVERT(VARCHAR, GETDATE(), 23), 7) AS YearMonth;

This makes FORMAT very powerful in multilingual applications. However, keep in mind that it relies on the .NET CLR under the hood. If CLR integration is disabled in your SQL Server environment, this function may not be available.
For more information and examples on FORMAT, check out this reference from Microsoft.

FORMAT vs. CONVERT: Which one should you use?

Both FORMAT and CONVERT can turn dates into strings, but they are not equal. One is faster and simpler, while the other is more flexible and culture-aware. Let’s see how they compare.

Syntax and flexibility comparison

With CONVERT, you rely on numeric style codes. They are easy to use but limited to what SQL Server gives you. For example:

SELECT CONVERT(VARCHAR, GETDATE(), 103); -- 29/03/2021

That’s quick, but if you want to move the month or day around in a custom way, you’re stuck.

FORMAT is different. It works just like .NET, so you can build your own patterns or even pass a culture. For example:

SELECT FORMAT(GETDATE(), 'D', 'fr-FR'); -- lundi 29 mars 2021

This makes FORMAT much more flexible, especially for multilingual apps.

Performance benchmarks (FORMAT vs. CONVERT)

Here’s where CONVERT really shines. It’s a native SQL function and runs very fast, even across big result sets.

FORMAT, on the other hand, depends on the .NET CLR. That extra layer makes it often several times slower than CONVERT. If you run it on thousands of rows, you’ll notice the difference.

So the difference is clear: use CONVERT when performance matters, and reach for FORMAT only when you need features CONVERT doesn’t have.

Culture-awareness and localization use cases

CONVERT has a few culture-related styles (like 103 for British dates), but it doesn’t go further than that.FORMAT lets you go all the way. You can format the same date for English, French, or Japanese users without changing your SQL logic. For example:

SELECT FORMAT(GETDATE(), 'D', 'ja-JP'); -- 2021年3月29日

That makes it the better option when your app needs to respect a user’s local settings or language.

Let’s briefly compare CONVERT and FORMAT to clearly see where they stand against each other. 

Feature	CONVERT	FORMAT
Custom formatting	❌ Limited to styles	✅ Full flexibility (via string)
Culture-aware	❌ No	✅ Yes (‘fr-FR’, ‘en-US’)
Performance	✅ Fast	⚠️ Slower (relies on .NET CLR)
.NET dependency	❌ No	✅ Yes
Use cases	Reports, legacy apps	Web apps, multi-language UI

Now, we’ve identified four 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 for exchanging data related 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.

This is why ISO 8601 is widely considered an industry best practice:

• It guarantees interoperability between systems, regardless of regional settings.
  It is the default standard for API communication, making integrations smoother and less error-prone.
• It helps reduce bugs in multicultural environments, where month-day swaps or language differences often cause silent data issues.

Here are two quick examples of the style codes in action:

-- ISO 8601 date only
SELECT CONVERT(VARCHAR, GETDATE(), 126) AS ISO_DateTime;

-- ISO 8601 with UTC marker
SELECT CONVERT(VARCHAR, GETUTCDATE(), 127) AS ISO_DateTime_UTC;

ISO 8601 styles 126 & 127 explained

Typical outputs might look like this:

• 126 → 2021-03-09T14:35:12.123
• 127 → 2021-03-09T12:35:12.123Z

Here’s a quick example:

SELECT CONVERT(VARCHAR, GETDATE(), 126) AS ISO_126, 
       CONVERT(VARCHAR, GETUTCDATE(), 127) AS ISO_127;

Styles 126 and 127 are the bridge between SQL Server and other systems because they speak a “universal language” for date/time values.

Whether you’re exchanging data across APIs, working with cloud services, or storing dates for multilingual users, sticking to ISO 8601 will save you from countless conversion headaches.

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.

FAQ

What is the best way to format dates in SQL Server?
It depends on your needs. If you want performance and predefined patterns, use the CONVERT function with style codes. If you need more flexibility and cultural variations, use the FORMAT function. For integrations and long-term storage, stick to ISO 8601, which avoids ambiguity.

What is the difference between FORMAT and CONVERT in SQL?

• CONVERT is faster and offers over 100 predefined style codes, but is less flexible.
• FORMAT supports fully customizable patterns and localization, but it relies on the .NET CLR and can be slower.
  If performance matters most, go with CONVERT. If you need culture-aware formatting, FORMAT is the better choice.

How can I format SQL dates for international users?
Use FORMAT with a culture code, for example:

SELECT FORMAT(GETDATE(), 'D', 'fr-FR'); -- French
SELECT FORMAT(GETDATE(), 'D', 'en-US'); -- U.S. English

For data exchange across countries, avoid region-specific formats and rely on ISO 8601 (YYYY-MM-DD or YYYY-MM-DDTHH:MM:SS).

What is ISO 8601, and why is it important in SQL?
ISO 8601 is the international standard for date and time formats. In SQL Server, it’s available through CONVERT style codes 126 and 127. Its main advantage is consistency: it eliminates ambiguity caused by regional settings and is the preferred format for APIs, integrations, and multicultural environments.

How do I handle time zones when formatting dates in SQL Server?
Use the DATETIMEOFFSET type to store both the date/time and its offset from UTC. You can then convert between UTC and local time with functions like SWITCHOFFSET and AT TIME ZONE. For APIs and cloud integrations, it’s best to store values in UTC (often using ISO 8601 style 127 with the Z suffix) and convert to local time only when displaying to users.

Tags: sql date, sql server Last modified: March 19, 2026