How to Avoid Out-of-Range Errors When Converting VARCHAR to DATETIME in SQL Server?

SQL Server VARCHAR to DATETIME Conversion: Preventing Out-of-Range Errors

Converting VARCHAR strings representing dates (like 'mmddyyyy') to SQL Server 2008's DATETIME type often leads to "out-of-range value" errors when using CONVERT directly. This is because the input format doesn't match the expected DATETIME format.

The solution involves restructuring the VARCHAR string to a yyyymmdd format before conversion. Here's how:

<code class="language-sql">DECLARE @Date char(8)
SET @Date = '12312009'
SELECT CONVERT(datetime, RIGHT(@Date, 4) + LEFT(@Date, 2) + SUBSTRING(@Date, 3, 2))</code>

This code performs these steps:

1. A VARCHAR variable (@Date) holds the input date string.
2. RIGHT(@Date, 4) extracts the year (last four characters).
3. LEFT(@Date, 2) extracts the month (first two characters).
4. SUBSTRING(@Date, 3, 2) extracts the day (characters 3 and 4).
5. The year, month, and day are concatenated into yyyymmdd format.
6. CONVERT() finally transforms the correctly formatted string into a DATETIME value.

This method effectively avoids the out-of-range error, ensuring a successful conversion.

The above is the detailed content of How to Avoid Out-of-Range Errors When Converting VARCHAR to DATETIME in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!