SQL Server Convert String to Date: CAST, CONVERT, and TRY_CONVERT Examples

How to convert a string to a date in SQL Server

SQL Server offers several functions that convert text values into DATE, DATETIME, or DATETIME2 formats. These functions are CAST, CONVERT, and TRY_CONVERT. But before we proceed to explore these functions, let us clarify why we need to convert strings to dates at all.

First and foremost, a string of characters brings no semantic meaning to a database or application. It is impossible to perform arithmetic calculations with strings. Therefore, if some dates stored in the database are strings, they affect the calculations and the overall performance.

You need to convert such strings to dates for sorting, filtering, calculation, and storage, and do it accurately. In SQL Server, the CAST, CONVERT, and TRY_CONVERT functions perform this task. Below you can see how they work.

CAST is used for ANSI-standard conversion.

SELECT CAST('2026-06-26' AS date);

CONVERT performs the SQL Server-specific conversion with format/style support.

SELECT CONVERT(date, '23/06/2026', 103); -- dd/mm/yyyy

TRY_CONVERT works similarly to CONVERT, but it returns NULL on invalid input instead of an error.

SELECT TRY_CONVERT(date, '2026-06-23');

CAST vs CONVERT vs TRY_CONVERT

All three functions convert a value from a string to a date. However, they differ in syntax, flexibility, and error handling. The table below summarizes the key differences between these functions.

Convert a string to a date using CAST

CAST is the function that works across most SQL databases. It is the simplest conversion option, but it does not support style/formatting codes.

CAST string as DATE

The basic syntax is as follows.

CAST(expression AS data_type)

You can use this function to convert strings to different date types.

SELECT CAST('2026-06-30' AS DATE);

SELECT CAST('2026-06-23 14:30:00' AS DATETIME);

SELECT CAST('2026-06-23 14:30:00' AS DATETIME2);

In all these examples, CAST explicitly tells SQL Server that the value should be treated as a date and converted to the specified date types. Then these values can be used in date calculations, comparisons, and other scenarios.

CAST works best when the string is in a standard format such as YYYY-MM-DD or YYYY-MM-DD HH:MM:SS. For ISO date literals, SQL Server can even perform the string-to-date conversion automatically.

SELECT
  SalesOrderID
 ,OrderDate
 ,ShipDate
 ,Status
 ,SalesOrderNumber
FROM Sales.SalesOrderHeader
WHERE OrderDate = '2022-09-07';

When CAST may fail

CAST fails if date values are invalid. In this case, SQL Server produces an error and stops the query.

SELECT CAST('2026-02-30' AS DATE);

It also may fail because of ambiguous date formats; the result depends on the DATEFORMAT and language settings applied during the session.

SELECT CAST('06/07/2026' AS DATE);

It is better to avoid using CAST when input data may contain invalid dates or locale-dependent formats. ISO formats (YYYYMMDD or YYYY-MM-DD) are preferable.

Convert a string to a date using CONVERT

CONVERT is a SQL Server function that converts a string to a date or datetime value. Unlike CAST, it accepts style codes that instruct SQL Server how to interpret the string.

CONVERT syntax

The basic syntax is as follows.

CONVERT(data_type, expression, style)

Parameters:

• data_type — the target type, such as DATE
• expression — the value to convert
• style — an optional numeric code that defines the input string format

Convert DD/MM/YYYY to date

DD/MM/YYYY is the British/French date format. Use the style code 103 for correct interpretation. Otherwise, SQL Server may misinterpret the date or produce an error.

SELECT CONVERT(DATE, '23/06/2026', 103);

Convert MM/DD/YYYY to date

MM/DD/YYYY is the US date format. The 101 style code should be applied to tell SQL Server to treat the first number as the month.

SELECT CONVERT(DATE, '06/23/2026', 101);

Convert YYYYMMDD to date

YYYYMMDD is the ISO format defined by the style code 112. This format is unambiguous; thus, it is recommended generally for SQL Server date strings.

SELECT CONVERT(DATE, '20260623', 112);

CONVERT may fail due to invalid input values or a mismatch between the style code and the actual format. In such cases, SQL Server raises an error and stops processing the query.

Common SQL Server date style codes

The table below lists the most common style codes used in SQL Server for conversion strings to date formats.

Handle invalid date strings with TRY_CONVERT

TRY_CONVERT works like CONVERT, but it has one significant difference: it returns NULL instead of raising an error when a conversion fails. This specificity makes it suitable for operations with imported data or data input by users: it does not break the process even if invalid values are detected.

The basic syntax is as follows.

TRY_CONVERT(data_type, expression, style)

The ability of TRY_CONVERT to continue processing all rows even when some values are invalid is helpful in data validation, cleansing, and bad data detection scenarios. Let us see how it works in practice.

Return NULL instead of an error

Data stored in CSV or XLS files or entered directly by the user often contains invalid values. When SQL Server has to process such data with CAST or CONVERT, invalid values cause the system to raise an error and stop the query. In contrast, TRY_CONVERT returns NULL for such values, and the query continues processing rows. Thus, you can generate reports and validate data even if some records are malformed.

SELECT TRY_CONVERT(DATE, '31/02/2026', 103);

Find rows with invalid dates

TRY_CONVERT helps you identify records with invalid date strings before loading data into production. In practice, it means selecting rows where TRY_CONVERT returns NULL. This method is widely used for importing, validating, and cleaning data.

WHERE TRY_CONVERT(DATE, DateString, style) IS NULL

How dbForge Studio for SQL Server helps with date conversion queries

dbForge Studio for SQL Server is an AI-powered integrated development environment that supports the full database development lifecycle, from T-SQL coding to version control.

When converting strings to date types, dbForge Studio provides a practical environment for writing, testing, and troubleshooting conversion queries. You can run CAST, CONVERT, and TRY_CONVERT examples directly in the Studio's SQL Editor, review results, and identify invalid date values before applying changes to production data.

The integrated dbForge AI Assistant can explain conversion errors, suggest query improvements, and help resolve issues such as invalid date strings, incorrect style codes, or mixed regional date formats.

Try dbForge Studio for SQL Server free with a fully functional trial—evaluate it against your actual workload!

FAQ