ORACLE Casting DATE to TIMESTAMP WITH TIME ZONE WITH OFFSET

sql oracle timestamp-with-timezone
Farlop picture Farlop · Mar 7, 2014 · Viewed 82.9k times · Source

I need to cast a DATE value in a query to a TIMESTAMP WITH TIME ZONE, but currently I'm getting the TimeZone Region ('Europe / Paris') which is not valid to be used by EF.

For example, when doing this:

select CAST(FECHA AS TIMESTAMP WITH TIME ZONE) from test;

I currently get this output:

07/03/14 09:22:00,000000000 EUROPE/PARIS

But I need it to be like:

07/03/14 09:22:00,000000000 +01:00

Any idea how to accomplish this?

Answer

Vincent Malgrat picture Vincent Malgrat · Mar 7, 2014

You can cast the DATE to a TIMESTAMP, then use FROM_TZ to convert this timestamp to a timestamp with time zone:

SQL> SELECT from_tz(CAST (SYSDATE AS TIMESTAMP), '+01:00') tz FROM dual;
TZ
-------------------------------------------------
07/03/14 09:47:06,000000 +01:00

Related questions

Change the date and time stamp format for data in the table

We have a huge Oracle database that contains data which has date and time stamps. Right now we are using mm-dd-yyyy format for date and EST Time zone for time stamp. Now we need to change the date format as …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More