Post reply

converting an date from oracle to a regular datetime in sql

  • 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?

  • 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

  • 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]

  • July 17, 2013 at 4:51 pm

    #1633478

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

  • 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

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

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