Siebel SQLs/Error Messages >  Change column from Date format to UTC format in Oracle Database

Change column from Date format to UTC format in Oracle Database

I thought it would be easy to find how to do this. It took me almost 30 minutes in Google search to find the correct solution so I am sharing this.

The job was to change the Date format in column ACT_CREATED_DT from Siebel.s_evt_act   from Date to UTC Date format.

SYS_EXTRACT_UTC   function kept showing up in Google search but it does not change the data in a table. If you provide a date, it can show that as UTC. Even a select like select  SYS_EXTRACT_UTC   (ACT_CREATED_DT) from Siebel.s_evt_act   was throwing errors.

 

If you want to just see the Data with an SQL, you can run this:

select cast(ACT_CREATED_DT as timestamp with time zone) at time zone 'UTC' from siebel.s_evt_act;

 

If you want to change data in your Oracle Table, this is the SQL format:

UPDATE tab

    SET TEXT = (SELECT text

                FROM tab t2

                WHERE tab.id = t2.id

               )

--where clauses for the main table

    WHERE tab. ACT_CREATED_DT IS NOT NULL;

 

So the final SQL is

UPDATE siebel. s_evt_act

    SET siebel. s_evt_act. ACT_CREATED_DT = (SELECT cast(ACT_CREATED_DT as timestamp with time zone) at time zone 'UTC'

                FROM siebel. s_evt_act t2

                WHERE siebel. s_evt_act.row_id = t2.ROW_ID

               )

    where siebel. s_evt_act.duration_hrs <> '0' AND

                      siebel.s_srv_req.ACT_CREATED_DT IS NOT NULL;