SQL Twist date

1

Hi,

Im facing a really bizarre issue. I have a Oracle DB linked to a SQL server DB. Everything looks fine, but the dates are totally mixed up. In Oracle the dates format are DD-MM-YYYY in SQL are YYYY-MM-DD.

When the day is less than 12 days in Oracle, in SQL is all fine EX. ORACLE 13-FEB-2016--> SQL 2016-02-13.
The funny thing happens when the dates are greater than 12 days EX in Oracle 01-FEB-2016 --.SQL 2016-01-02,
so day and month are twisted.

I already have change the language, the date format and nothing helped.

Please advise any clarification for the sake of knowledge.

Thanks

3

what is the Oracle datatype for the date? I assume it's not datetime. If it's varchar or something else, then you need to translate between the two, perhaps in a view.

4

In SQL Server, before loading the Oracle data, issue this command:

SET DATEFORMAT DMY

--load Oracle data
--Then, after getting the Oracle data, set it back to SQL's (English) default:

SET DATEFORMAT MDY

5

This might help.

https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html

6

Is date