how to convert date in varchar format into datetime

  • Hello Everyone,

    I have table in which date is in varchar format i.e. '29/09/2013'

    now I want to insert the records in table1 into table2 where datatype of datecolumn is datetime so I need to convert the date in Datetime format for that I am writing the following query:

    Select convert(datetime,ltrim(rtrim(replace('28/09/2013','','')))+' 00:00:00.000') from TMP_MAPPING

    but I am getting error massege:

    Msg 242, Level 16, State 3, Line 3

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

    can any on help me how can I import the data into my main table.

  • Look in books online for the format codes for CONVERT and use the one that matches the data that you have.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    Please visit this page to know the options available in Convert function

    http://technet.microsoft.com/en-us/library/ms187928.aspx

  • CONVERT function will help u in this.(different styling).

  • SELECT CONVERT(DATETIME,'29/09/2013',103)

    Use the convert function and the date style that suits best for your operations. 🙂

  • If I'm reading this right, you're not trying to CONVERT a DATETIME to a dd/mm/yyyy format, rather you're trying to go the other way.

    Try this:

    WITH SampleData (d) AS

    (

    SELECT '29/09/2013'

    UNION ALL SELECT '1/09/2013'

    UNION ALL SELECT '02/4/2013'

    UNION ALL SELECT '3/3/2013'

    )

    SELECT yy, dd, mm, CAST(yy + '-' + mm + '-' + dd AS DATETIME)

    FROM SampleData

    CROSS APPLY

    (

    SELECT yy=RIGHT(d, CHARINDEX('/', REVERSE(d))-1)

    ,dd=LEFT(d, CHARINDEX('/', d)-1)

    ,mm=REPLACE(SUBSTRING(d, CHARINDEX('/', d) + 1, 2), '/', '')

    ) a;


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply