Home Forums SQL Server 2008 SQL Server Newbies Trying to convert varchar to datetime RE: Trying to convert varchar to datetime

  • July 25, 2013 at 1:58 am

    #1635619

    savycara (7/24/2013)

    Actually 2011 is year and 4 is month which shoud be represented as 04 and date is 01 (which is to be added)

    I need this column data to be in style 101

    Your first post states that you want output as datetime. If you want your output as style 101, it would have to be varchar or char. Which do you want? Here's datetime:

    SELECT *

    FROM (SELECT strDate = '20114' UNION ALL SELECT '201112') d

    CROSS APPLY (SELECT strDate2 = STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01') x

    CROSS APPLY (SELECT dtDate = CONVERT(DATETIME,x.strDate2,112)) y

    CROSS APPLY (SELECT Result = CONVERT(DATETIME,STUFF(strDate,5,0,CASE LEN(strDate) WHEN 5 THEN '0' ELSE '' END)+'01',112)) z

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]