SQL convert string to date

In SQL Server, you can convert a string to a date using the CONVERT function or the CAST function. The format for converting a string to a date depends on the input string’s format.

Example

Here is a basic example using the CONVERT function:

DECLARE @DateString VARCHAR(20) = '2023-11-29';
DECLARE @DateValue DATE;

SET @DateValue = CONVERT(DATE, @DateString, 120);

SELECT @DateValue AS ConvertedDate;

In this example, the CONVERT function is used with the style code 120, which represents the ODBC canonical date-time format. You can adjust the style code based on your input string’s format.

Alternatively, you can use the CAST function:

DECLARE @DateString VARCHAR(20) = '2023-11-29';
DECLARE @DateValue DATE;

SET @DateValue = CAST(@DateString AS DATE);

SELECT @DateValue AS ConvertedDate;

This example demonstrates how to use the CAST function to convert a string to a date. The CAST function is generally simpler and more concise but may be less flexible than the CONVERT function if you need to handle different date formats.

Remember that the success of the conversion depends on the format of the input string. If the string is not in a recognizable date format, the conversion may fail, and you might need to handle such cases with additional error checking or data cleansing steps.