sqlsql-serversql-server-2008t-sqltype-conversion

Convert varchar into datetime in SQL Server


How do I convert a string of format mmddyyyy into datetime in SQL Server 2008?

My target column is in DateTime

I have tried with Convert and most of the Date style values however I get an error message:

'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'


Solution

  • OP wants mmddyy and a plain convert will not work for that:

    select convert(datetime,'12312009')
    
    Msg 242, Level 16, State 3, Line 1 
    The conversion of a char data type to a datetime data type resulted in 
    an out-of-range datetime value
    

    so try this:

    DECLARE @Date char(8)
    set @Date='12312009'
    SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))
    

    OUTPUT:

    -----------------------
    2009-12-31 00:00:00.000
    
    (1 row(s) affected)