How To Convert Date To String In SQL Server

In this comprehensive article, I’ll walk you through everything you need to know about converting dates to strings in SQL Server.

How To Convert Date To String In SQL Server

Let’s explore all the methods available to convert dates to strings in SQL Server.

Approach 1: Using the CONVERT() Function

The CONVERT() function is the traditional and most versatile approach for converting dates to strings in SQL Server. It gives you precise control over the output format through style codes.

Syntax

CONVERT(VARCHAR, date_value [, style_code])

The function takes three parameters:

• The target data type (usually VARCHAR)
• The date value to convert
• An optional style code that determines the output format

Style Codes for Date Formatting

Here’s a table of commonly used style codes

Example

Let’s examine the usage of the CONVERT() function. We can execute the following query to convert the US standard format (MM/DD/YYYY).

-- Get current date and time
DECLARE @CurrentDateTime DATETIME = GETDATE()
-- US standard format (mm/dd/yyyy)
SELECT CONVERT(VARCHAR, @CurrentDateTime, 101) AS US_Standard_Format

After executing the above query, I obtained the expected output, as shown in the screenshot below.

This function is extremely flexible and allows you to convert a datetime to a string in SQL Server with precise formatting control.

Approach 2: Using CAST() Function

The CAST() function offers a simpler yet less flexible approach to achieve this.

Syntax

CAST(date_value AS VARCHAR)

Example

DECLARE @CurrentDate DATE = GETDATE()
SELECT CAST(@CurrentDate AS VARCHAR) AS StringDate

After executing the above query, I obtained the expected output, as shown in the screenshot below.

The limitation of CAST() is that it doesn’t allow format customization. It will use the default format based on your SQL Server settings. Generally, when converting a date to a string using CAST(), you’ll get an ISO format (YYYY-MM-DD).

CAST() is a standard SQL function that works across different database systems, making it a good choice for cross-platform compatibility.

Approach 3: Using the FORMAT() Function (SQL Server 2012 and Above)

For those using SQL Server 2012 or newer versions, the FORMAT() function offers a more flexible way to format dates as strings.

Syntax

FORMAT(date_value, format_string [, culture])

This function takes:

• The date value to convert
• A format string specifying the output format
• An optional culture parameter (like ‘en-US’ for the United States)

Format Patterns for FORMAT()

The FORMAT() function uses the following patterns.

Example

We can execute the following query for the US date format with FORMAT().

DECLARE @CurrentDateTime DATETIME = GETDATE()
-- US date format with FORMAT()
SELECT FORMAT(@CurrentDateTime, 'MM/dd/yyyy') AS US_Date

After executing the above query, I obtained the expected output, as shown in the screenshot below.

The FORMAT() function provides a more intuitive approach similar to .NET’s string formatting, which many developers find easier to remember than style codes.

Method 4: Using DATENAME() and DATEPART() for Custom Formatting

You can combine the DATENAME() and DATEPART() functions with string concatenation.

Example

DECLARE @CurrentDate DATETIME = GETDATE()

SELECT 
    DATENAME(MONTH, @CurrentDate) + ' ' + 
    CAST(DAY(@CurrentDate) AS VARCHAR) + ', ' + 
    CAST(YEAR(@CurrentDate) AS VARCHAR) AS Custom_Date_Format

After executing the above query, I obtained the expected output, as shown in the screenshot below.

Performance Considerations

Here are some considerations

1. CONVERT() vs FORMAT(): The CONVERT() function generally performs better than FORMAT() for large datasets, as FORMAT() has higher overhead due to its culture-aware processing.
2. String Length: Always specify the length of your VARCHAR when using CONVERT() to avoid truncation:CONVERT(VARCHAR(20), @DateValue, 101)
3. Indexing: Remember that once converted to strings, dates lose their advantages for indexing and sorting.

Use Cases for Date to String Conversion

1. Creating a User-Friendly Report

SELECT 
    Order_id,
    Order_name,
    CONVERT(VARCHAR, Order_Date, 101) AS OrderDate
FROM Orders
WHERE Order_Date > DATEADD(MONTH, -3, GETDATE())

After executing the above query, I got the expected output

2. Generating Filename Timestamps

DECLARE @FileName VARCHAR(100)
SET @FileName = 'Backup_' + CONVERT(VARCHAR, GETDATE(), 112) + '.bak'
SELECT @FileName

I received the expected output after executing the query as shown in the screenshot below.

3. Generating ISO 8601 Timestamps for Logs

SELECT 'Event logged at: ' + CONVERT(VARCHAR, GETDATE(), 126)
-- Results in: Event logged at: 2025-06-11T14:30:00.000

I got the expected output after executing the above query.

Conclusion

Converting dates to strings in SQL Server is essential for every database developer to know. The methods I’ve covered in this article give you complete details on the same.

• Use CONVERT() for efficient, style-code-based formatting
• Use CAST() for simple, standard conversions
• Use FORMAT() for intuitive, culture-aware formatting (SQL Server 2012+)
• Use DATENAME()/DATEPART() for complete custom control

You may also like the following articles.

• SQL Server Date Math
• SQL Server Date Data Types
• SQL Server Subtract Days From Date
• SQL Server Add Time To Date
• SQL Server Date In Where Clause

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.