converting an date from oracle to a regular datetime in sql

• smrobin

  Ten Centuries

  Points: 1003

  More actions

  July 17, 2013 at 4:20 pm

  #276119

  So I have some data that I am importing in from Oracle - and the date is coming in the following way:

  25-JUN-13 12.01.15.096000000 AM

  If I run the following:

  select convert(date,'25-JUN-13 12.01.15.096000000 AM') as [date]

  I get the following error:

  Conversion failed when converting date and/or time from character string

  If I run the following:

  select convert(datetime,substring('25-JUN-13 12.01.15.096000000 AM',0,10))

  It returns the data in the format: 2013-06-25 00:00:00.000

  I need to be able to grab the complete time stamp and put in a datetime format.

  Any ideas on how to do that?

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 17, 2013 at 4:29 pm

  #1633474

  Like this?

  SELECT CONVERT( datetime2, STUFF(STUFF(OraDate,13,1,':'),16,1,':'))

  FROM (SELECT '25-JUN-13 12.01.15.096000000 AM' AS OraDate) O

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  July 17, 2013 at 4:31 pm

  #1633475

  DECLARE @oracledate varchar(50) = '25-JUN-13 12.01.15.096000000 AM'

  select try_convert(datetime2, substring(@oracledate, 1, 12) + ':' +

  substring(@oracledate, 14, 2) + ':' +

  substring(@oracledate, 17, 50))

  But this will hot work of if there is not leading zeores for all one-digit number.

  And one could ask why Oracle produces this value in the first place. Surely Oracle has other options?

  [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

• smrobin

  Ten Centuries

  Points: 1003

  More actions

  July 17, 2013 at 4:51 pm

  #1633478

  Thanks guys - those both worked! Thanks for the help!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  July 17, 2013 at 5:25 pm

  #1633480

  If there are no leading zeroes for one digit day, here's an alternative.

  SELECT CONVERT( datetime2, PARSENAME(OraDate,4) + ':' + PARSENAME(OraDate,3) + ':' + PARSENAME(OraDate,2) + '.' + PARSENAME(OraDate,1))

  FROM (SELECT '25-JUN-13 12.01.15.096000000 AM' AS OraDate) O

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2