How Can I Successfully Convert mmddyyyy Varchar Strings to DateTime in SQL Server 2008?

Effective way to convert mmddyyyy varchar string to datetime in SQL Server 2008

In SQL Server 2008, converting a mmddyyyy format string representing a date to a datetime data type may encounter a conversion error. Here's how to fix this:

Parse and convert strings

Conversion using the CONVERT function directly may fail with an out-of-range error. You should instead manually parse the string into its component parts (month, day, year) and then construct the datetime value:

<code class="language-sql">DECLARE @Date char(8)
SET @Date = '12312009'

SELECT CONVERT(datetime,
                RIGHT(@Date, 4) + SUBSTRING(@Date, 3, 2) + LEFT(@Date, 2))</code>

Example: Convert '12312009' to DateTime

Using the above method, the string '12312009' can be converted to a datetime value as follows:

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

Output results

The output of the above query will be:

<code>-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)</code>

Please note that in order to ensure the correctness of the conversion, the order of string processing has been adjusted in the code, placing the year at the front, the month in the middle, and the date at the end, which is different from the default processing method of the CONVERT function. consistent.

The above is the detailed content of How Can I Successfully Convert mmddyyyy Varchar Strings to DateTime in SQL Server 2008?. For more information, please follow other related articles on the PHP Chinese website!