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

Question

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 yyyy-mm-dd and time stamps should be GMT timezone. We need to change already existing data in the tables to this new format as well as newly inserted data.

Any help on how to achieve this?

My question is more similar to http://stackoverflow.com/questions/11106703/change-timezone-component-of-timestamp-with-timezone-in-oracle. In addition i need date format change too. — Abhishek kumar, Jan 03 '13 at 18:53
As I explained in this answer to another recent question, the date format is a *display property* and has nothing to do with how the date is stored: http://stackoverflow.com/a/14144928/146325 — APC, Jan 03 '13 at 20:18
@APC Yes for `date`. But `timestamp with time zone` the time zone information is actually stored in the database. See my answer. — kmkaplan, Jan 04 '13 at 14:00
@kmkaplan - I was merely pointing out the OP's misconception regarding date format. Obviously the timezone is data not presentation, and so the conversion needs to be addressed. I don't believe my comment says otherwise. — APC, Jan 04 '13 at 14:34

kmkaplan · Accepted Answer · 2013-01-13T15:33:36.600

Changing the date format is just a matter of presentation. When selecting from you database, apply a format:

select to_char(sysdate, 'YYYY-MM-DD') from dual;

To get this format by default, set it in your session’s NLS_DATE_FORMAT parameter:

alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD';

You can also set the NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT. See Setting Up a Globalization Support Environment in Oracle’s documentation.

Regarding time zones, normally it is the application that inserts the timestamp with time zone in the database that chooses what time zone it puts. But the application that queries these timestamps can request to get them back in another time zone. Compare the following queries:

select systimestamp from dual; -- This gives you the timestamp in the server configured time zone
select systimestamp at time zone 'EST' from dual; -- This is in your EST time zone
select systimestamp at time zone '+00:00' from dual; -- Finally the same timestamp in UTC.

To force a certain time zone you can force it with an after insert or update trigger. Something like:

create or replace trigger my_table_force_utc_timestamps
before insert or update on my_table
for each row
begin
    :new.my_timestamp_with_tz := :new.my_timestamp_with_tz at time zone '+00:00';
end;
/

If you really want to convert your timestamps to UTC you can try:

update my_table set my_timestamp_with_tz = my_timestamp_with_tz at time zone '+00:00';

Edit: to change sysdate time zone before inserting into tables you must in fact use systimestamp at time zone … that will be cast to a date value:

insert into my_table (date_column) values (systimestamp at time zone '+00:00');

You can read more about time zones in Oracle’s documentation Datetime and Time Zone Parameters and Environment Variables. With the example table_dt this would give:

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session modifiee.

SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);

Table creee.

SQL> insert into table_dt values (1, systimestamp);

1 ligne creee.

SQL> insert into table_dt values (2, systimestamp at time zone '+00:00');

1 ligne creee.

SQL> select * from table_dt;

      C_ID C_DT
---------- ----------------------
         1 13-JANV.-2013 16:31:41
         2 13-JANV.-2013 15:32:08

This answer would be better if it majored on changing the timezones, which is the important matter. THe date format is a red herring. — APC, Jan 04 '13 at 14:39
@APC You mean like reordering the two parts? Or using some formatting tricks to make it ovious that the second part is the big one? — kmkaplan, Jan 04 '13 at 14:43
@kmkaplan-today i checked my columns are of DATE type only with time in EST timezone. From my java side i am inserting sysdate into these columns. How can i change sysdate time zone before inserting into tables? — Abhishek kumar, Jan 12 '13 at 17:35
@kmkaplan-can u explain it with table_dt table as in the oracle link. I tried this insert query insert into table_dt values (9,sysdate at time zone '+00:00') as well as insert into table_dt(c_dt) values(sysdate at time zone '+00:00'). But getting this error ORA30084- invalid data type for datetime primary with time zone modifier. — Abhishek kumar, Jan 12 '13 at 18:34
@Abhishekkumar That’s my fault. Dates do *not* have a time zone. To be able to operate on time zones in Oracle you need a `timestamp`. So you need to insert `systimestamp at time zone '+00:00'`. I edited the answer to fix this and to show an example with the `table_dt` from Oracle’s documentation. — kmkaplan, Jan 13 '13 at 15:35

rs. · Answer 2 · 2013-01-03T18:58:01.050

1

You can use new_time oracle function

SELECT to_char(new_time(to_date('01-03-2013','mm-dd-yyyy HH24:MI'), 
                        'EST', 'GMT'),'yyyy-mm-dd HH24:MI') d
FROM DUAL;

Use this to test on your database:

SELECT to_char(new_time(sysdate, 'EST', 'GMT'),'yyyy-mm-dd HH24:MI') d
FROM DUAL;

edited Jan 03 '13 at 18:58

answered Jan 03 '13 at 18:52

rs.

26,707
12
68
90

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

2 Answers2