For a project at a customer I had to load data from a SQL SERVER database to a Oracle database. It turned out that the SQL SERVER database stored the date and time data in UTC. This became apparent when the data was reported via Business Objects and all the date time data was off by 1 or 2 hours depending on the daylight saving time. So how do we fix this ?
We cannot just add +2 hours (we are in +2 timezone) because this will fail when daylight saving is active.
It would be possible to keep track when daylight saving is active but that changes overtime and that would mean that you have to maintain the function.
So this is how i fixed it :
Step 1:
Create a connection in powercenter for your target database and set the connection attribute ‘Connection Environment SQL’ to :
ALTER SESSION SET TIME_ZONE=’UTC’
This will make sure that the data is written as UTC datetime stamp in Oracle and not as the local timezone.
If you need to convert from another timezone you need to enter that timezone instead of UTC.
Step 2:
Read the datetime column from SQL SERVER via powercenter and insert this data into a oracle column with datatype ‘Timestamp with local timezone’ using the connection defined above.
Step 3:
When you query the data from Oracle you need to make sure you set your session to (im my case) CET.
Because the datatype is ‘Timestamp with local timezone’ Oracle will internally convert the UTC datetime to CET. CET is also daylight saving time aware so the dates are converted correctly with or without daylight saving active.
When queried it will display the data in the local timezone (CET in this case).
If you are using Business Objects to read the data you can set your session in the universe and the report will show the correct data.
Example Daylight saving :
- 18-08-2014 12:00:00 is entered in the application (daylight saving active so +1)
- This is stored in SQL SERVER as 18-08-2014 11:00:00 (UTC)
- Set writing session to UTC.
- Insert into oracle as 18-08-2014 11:00:00 (UTC)
- Set query session to CET
- Query Oracle and 18-08-2014 12:00:00 will be displayed.(daylight saving active so +1)
Example No Daylight saving :
- 01-01-2014 12:00:00 is entered in the application (Timezone +2)
- This is stored in SQL SERVER as 01-01-2014 10:00:00 (UTC)
- Set writing session to UTC.
- Insert into oracle as 01-01-2014 10:00:00 (UTC)
- Set query session to CET
- Query Oracle and 01-01-2014 12:00:00 will be displayed.(daylight saving not active so +2)