How to Convert a Date to a String in SQL Server

There are a few reasons you might need to convert a date to a string in SQL Server. Maybe you need a date in a specific format for a report. Maybe you’re concatenating it with other text. Maybe an external system expects dates as strings. Whatever the reason, SQL Server gives you several ways to do it, and the right one depends on what you’re trying to achieve.

This article covers four functions: FORMAT(), CONVERT(), CAST(), and STR().

Using `FORMAT()`

The FORMAT() function is the most flexible option. You pass in a date value and a format string, and it returns the result as text. The syntax is:

FORMAT(value, format [, culture])

Some examples:

DECLARE @d DATE = '2026-03-24';

SELECT
    FORMAT(@d, 'MM/dd/yyyy')     AS [MM/dd/yyyy],
    FORMAT(@d, 'dd-MM-yyyy')     AS [dd-MM-yyyy],
    FORMAT(@d, 'yyyy-MM-dd')     AS [yyyy-MM-dd],
    FORMAT(@d, 'MMMM dd, yyyy')  AS [MMMM dd, yyyy],
    FORMAT(@d, 'dddd')           AS [dddd],
    FORMAT(@d, 'D')              AS [D];

Result:

MM/dd/yyyy  dd-MM-yyyy  yyyy-MM-dd  MMMM dd, yyyy   dddd     D                      
----------  ----------  ----------  --------------  -------  -----------------------
03/24/2026  24-03-2026  2026-03-24  March 24, 2026  Tuesday  Tuesday, March 24, 2026

The format string uses .NET date format tokens. Here are the most common ones:

Token	Description	Example
`yyyy`	Four-digit year	2026
`yy`	Two-digit year	26
`MM`	Two-digit month	03
`MMMM`	Full month name	March
`MMM`	Abbreviated month name	Mar
`dd`	Two-digit day	24
`dddd`	Full day name	Tuesday
`D`	Long date pattern (locale-dependent)	Tuesday, March 24, 2026

Capitalization matters. MM is month, mm is minutes. Getting those mixed up is a common mistake.

FORMAT() also accepts an optional third argument — a culture code — which formats the date according to a specific locale. The 'D' format string is a good way to see this in action, since it produces the full long date and the output changes noticeably between languages:

DECLARE @d DATE = '2026-03-24';

SELECT
    FORMAT(@d, 'D', 'en-US')  AS [en-US],
    FORMAT(@d, 'D', 'de-DE')  AS [de-DE],
    FORMAT(@d, 'D', 'fr-FR')  AS [fr-FR];

Result:

en-US                    de-DE                    fr-FR             
-----------------------  -----------------------  ------------------
Tuesday, March 24, 2026  Dienstag, 24. März 2026  mardi 24 mars 2026

This is useful when your output needs to match a regional standard. Without the culture argument, SQL Server uses the language setting of the current session.

The main downside of FORMAT() is performance. It’s slower than CONVERT() because it uses .NET formatting under the hood. For small datasets that rarely matters, but on large queries it can add up.

Using `CONVERT()`

CONVERT() takes a different approach. Instead of a format string, you pass in a numeric style code that maps to a predefined date format. The syntax goes like this:

CONVERT(VARCHAR, date_value, style_code)

Here are the most useful style codes for dates:

Style Code	Format	Example Output
101	mm/dd/yyyy (US)	03/24/2026
103	dd/mm/yyyy (UK/European)	24/03/2026
112	yyyymmdd (ISO compact)	20260324
120	yyyy-mm-dd hh:mi:ss	2026-03-24 14:30:00
106	dd Mon yyyy	24 Mar 2026
107	Mon dd, yyyy	Mar 24, 2026

Here are some examples:

DECLARE @d DATE = '2026-03-24';

SELECT
    CONVERT(VARCHAR, @d, 101)  AS [US_Format],
    CONVERT(VARCHAR, @d, 103)  AS [UK_Format],
    CONVERT(VARCHAR, @d, 112)  AS [ISO_Compact],
    CONVERT(VARCHAR, @d, 106)  AS [Day_Mon_Year],
    CONVERT(VARCHAR, @d, 107)  AS [Mon_Day_Year];

Result:

US_Format   UK_Format   ISO_Compact  Day_Mon_Year  Mon_Day_Year
----------  ----------  -----------  ------------  ------------
03/24/2026  24/03/2026  20260324     24 Mar 2026   Mar 24, 2026

CONVERT() is faster than FORMAT() and you’ll see it everywhere in older SQL code. The limitation is that you’re restricted to its fixed list of style codes. If none of them match the format you need, FORMAT() is the more flexible option.

One thing worth knowing is that if you omit the style code entirely, CONVERT() falls back on the session’s DATEFORMAT setting to determine the output format (whose default value is itself determined by the session’s language). The same issue applies when casting a DATETIME without a format. Always specify a style code to get consistent, predictable results.

Using `CAST()`

CAST() is the simplest of the three. It converts a date to a string, but gives you no control over the output format. You get whatever SQL Server’s default string representation is for that data type:

DECLARE @d DATE = '2026-03-24'

SELECT
    CAST(@d AS VARCHAR)  AS [Date_As_String];

Result:

Date_As_String
--------------
2026-03-24

For a DATE value, that default happens to be yyyy-MM-dd, which is actually pretty clean. For a DATETIME value it includes the time component too:

DECLARE @dt DATETIME = '2026-03-24 14:30:00';

SELECT
    CAST(@dt AS VARCHAR)  AS [DateTime_As_String];

Result:

DateTime_As_String 
-------------------
Mar 24 2026  2:30PM

As with CONVERT() when no style code is specified, the output here is controlled by the session’s DATEFORMAT setting, so it isn’t guaranteed to be consistent across servers or users. The DATE example above happens to produce a reliable ISO format regardless of session settings, but for DATETIME and other date types that’s not the case.

Use CAST() when you’re working with plain DATE values and the ISO default suits your needs. Otherwise FORMAT() or CONVERT() with an explicit style code are the safer options.

Using `STR()`

The STR() function is designed for converting numbers to strings, not dates. You won’t use it often for date conversion, but it does come up occasionally when people want to extract a numeric date part and convert it to text in one step.

DECLARE @d DATE = '2026-03-24';

SELECT
    STR(YEAR(@d))   AS [Year],
    STR(MONTH(@d))  AS [Month],
    STR(DAY(@d))    AS [Day];

Result:

Year        Month       Day       
----------  ----------  ----------
      2026           3          24

One thing to watch out for is that STR() pads its output with spaces by default to fill a fixed width, which can cause alignment issues if you’re concatenating the result with other strings. You’ll often need to wrap it in LTRIM() to strip the leading spaces:

DECLARE @d DATE = '2026-03-24';

SELECT
    STR(MONTH(@d))        AS [With_Padding],
    LTRIM(STR(MONTH(@d))) AS [Without_Padding];

Result:

With_Padding  Without_Padding
------------  ---------------
         3    3

For most date-to-string needs, STR() is not the right tool. It’s worth knowing about, but FORMAT() handles everything STR() can do here and does it more cleanly.

Which Function Should You Use?

Here’s a simple way to decide:

Need a specific date format? Use FORMAT(). It’s the most flexible and readable option.
Working with large datasets where performance matters? Use CONVERT(), as long as one of its built-in style codes covers your needs.
Just need a plain string with no specific formatting? Use CAST(). It’s simple and does the job.
Extracting a numeric date part as text? FORMAT() is cleaner than STR() for this, but STR() with LTRIM() works if needed.

One Thing to Keep in Mind

Whichever function you use, the result is a string, not a date. That means you can’t do date arithmetic on it, and filtering or sorting on the converted value won’t behave the way it would on a real date column. Do any date-based filtering before you convert, and apply the conversion in the SELECT part of your query.

Using FORMAT()

Using CONVERT()

Using CAST()

Using STR()

Which Function Should You Use?

One Thing to Keep in Mind

Using `FORMAT()`

Using `CONVERT()`

Using `CAST()`

Using `STR()`