Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

converting an date from oracle to a regular datetime in sql Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 4:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 2:01 PM
Points: 14, Visits: 88
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?

Post #1474831
Posted Wednesday, July 17, 2013 4:29 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 4,066, Visits: 9,235
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474832
Posted Wednesday, July 17, 2013 4:31 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 1:19 PM
Points: 825, Visits: 756
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?


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1474833
Posted Wednesday, July 17, 2013 4:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 2:01 PM
Points: 14, Visits: 88
Thanks guys - those both worked! Thanks for the help!
Post #1474836
Posted Wednesday, July 17, 2013 5:25 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:13 AM
Points: 4,066, Visits: 9,235
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474839
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse