how to convert date in varchar format into datetime

• amitsingh308

  Ten Centuries

  Points: 1287

  More actions

  September 28, 2013 at 4:56 am

  #298416

  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.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 28, 2013 at 5:37 am

  #1654280

  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

• ParulPrabu

  SSCrazy

  Points: 2271

  More actions

  September 30, 2013 at 12:30 am

  #1654424

  Hi,

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

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

• Mr. Kapsicum

  SSCertifiable

  Points: 6128

  More actions

  September 30, 2013 at 12:45 am

  #1654431

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

• saichaitanya55

  Valued Member

  Points: 74

  More actions

  September 30, 2013 at 1:18 am

  #1654443

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

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

• Dwain Camps

  SSC Guru

  Points: 86968

  More actions

  October 2, 2013 at 10:29 pm

  #1655384

  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