How can I convert a SQL Server date format to Oracle?

views:

842

answers:

+2 Q:

How can I convert a SQL Server date format to Oracle?

I am working on migration of data from an old system to a new system. As part of migration, the data from the legacy system, (stored in files) is pumped into MS SQL Server. Now my app runs on Oracle. I'm having a problem with the date/timestamp.

The timestamp format in MS SQL Server data is:

2008.12.23 00:00:00

Oracle expects:

23/12/2008 00:00:00

23-DEC-2008 00:00:00

What would be the best way to import the data? Oracle's to_date() function didn't work as I thought it would.

+1 A:

You can put a second parameter on the to_date function to specify in what format the incoming data is. You will likely have to make SQL server pump the data out as a string.

http://www.techonthenet.com/oracle/functions/to_date.php

belgariontheking 2009-06-10 16:19:51

I was using the incorrect formatting string, hence the errors.

Sathya 2009-06-10 16:26:39

Use CONVERT to convert your internal datetime columns to text in whatever format you wish on the SQL Server side.

Cade Roux 2009-06-10 16:21:27

convert requires write access to MS SQL Server d/b right? I have read only access.

Sathya 2009-06-10 16:29:24

Do it on the extract: SELECT CONVERT(varchar, dtcol, 100) AS dt_txt FROM tbl - nothing but table and column read access required.

Cade Roux 2009-06-10 16:37:56

DCookie's method is simpler, as I can do the formatting as and when I'm inserting the data. Will keep this in mind, will probably be using this method elsewhere.

Sathya 2009-06-10 16:46:00

+3 A:

I assume you're using insert statements?

Convert your dates using:

TO_DATE(sql_server_value,'YYYY.MM.DD HH24:MI:SS')

DCookie 2009-06-10 16:21:38

This worked. Thanks. I was using TO_DATE(sql_server_value,'DD-MON-YY HH24:MI:SS')

Sathya 2009-06-10 16:25:33

Yes... the format string describes the format of the value you're converting. If there's periods in the string, you have to specify periods in the format.

DCookie 2009-06-10 16:27:44

I was under the notion that format string to be used is that of the destination format string. Thanks for clearing this up.

Sathya 2009-06-10 16:44:47

Remember that when you're converting to an Oracle DATE type, there is no internal "format" - it's just a number. You apply formatting to describe how it's output or to describe the format of an input string.

DCookie 2009-06-10 16:55:52

Will keep that in mind.

Sathya 2009-06-10 18:32:17

ansaurus

tags:

views:

answers:

How can I convert a SQL Server date format to Oracle?

related questions