How to Convert mmddyyyy VARCHAR Strings to DATETIME in SQL Server?

Handling mmddyyyy VARCHAR to DATETIME Conversion in SQL Server

Directly converting mmddyyyy formatted strings to DATETIME in SQL Server often leads to errors. This is because the standard CONVERT function expects a specific date format. This article provides a robust solution for this common problem.

Here's a reliable method:

DECLARE @Date VARCHAR(8)
SET @Date = '12312009'
SELECT CONVERT(DATETIME, RIGHT(@Date, 4) + '-' + LEFT(@Date, 2) + '-' + SUBSTRING(@Date, 3, 2))

This code functions as follows:

1. The RIGHT function extracts the year (last four characters).
2. The LEFT function extracts the month (first two characters).
3. SUBSTRING extracts the day (characters 3 and 4).
4. These parts are concatenated with hyphens to create a yyyy-mm-dd string.
5. CONVERT transforms this string into a DATETIME value.

This technique overcomes the limitations of a simple CONVERT by explicitly rearranging the string components into the correct order for SQL Server's DATETIME interpretation, effectively resolving out-of-range errors. This ensures accurate and reliable conversion of mmddyyyy strings to the DATETIME data type.

The above is the detailed content of How to Convert mmddyyyy VARCHAR Strings to DATETIME in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!