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;
|