Home > Database > Mysql Tutorial > How Can I Successfully Convert mmddyyyy Varchar Strings to DateTime in SQL Server 2008?

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

Mary-Kate Olsen
Release: 2025-01-13 10:15:42
Original
474 people have browsed it

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:

1

2

3

4

5

<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>

Copy after login

Example: Convert '12312009' to DateTime

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

1

2

<code class="language-sql">SELECT CONVERT(datetime,

                RIGHT('12312009', 4) + SUBSTRING('12312009', 3, 2) + LEFT('12312009', 2))</code>

Copy after login

Output results

The output of the above query will be:

1

2

3

4

<code>-----------------------

2009-12-31 00:00:00.000

 

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

Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template