2

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

or

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.

Improve this question

3 Answers 3

Reset to default
6

I assume you're using insert statements?

Convert your dates using:

TO\_DATE(sql\_server\_value,'YYYY.MM.DD HH24:MI:SS')
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This worked. Thanks. I was using TO_DATE(sql_server_value,'DD-MON-YY HH24:MI:SS')
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.
I was under the notion that format string to be used is that of the destination format string. Thanks for clearing this up.
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.
1

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

Improve this answer

1 Comment

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

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

Improve this answer

3 Comments

convert requires write access to MS SQL Server d/b right? I have read only access.
Do it on the extract: SELECT CONVERT(varchar, dtcol, 100) AS dt_txt FROM tbl - nothing but table and column read access required.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.