and more in a single search tool across platforms. Read the announcement here. |
06/02/2023 01:54 AM
Hi all,
In this query hear I need to convert the CURRENT_TIMESTAMP UTC time to EST time while inserting the record into the table. Can someone suggest how I can achieve this?
{"memberOf":["INSERT INTO PS_EAP_SUB_IN (EMPLID, OPRID, ROLENAME, EAP_TRANSACTIONID, EAP_ACTION, EAP_STATE, EAP_RETURN_VAL, LASTUPDDTTM) VALUES ('${user.employeeid}', UPPER('${user.systemUserName}'), '${task.entitlement_valueKey.entitlement_value}', (SELECT MAX(EAP_TRANSACTIONID) + 1 FROM PS_EAP_SUB_IN), 'I', 'A', ' ', CURRENT_TIMESTAMP)"]}
Regards,
Gopi.
Solved! Go to Solution.
06/02/2023 02:30 AM - edited 06/02/2023 02:33 AM
Hello @Gopi
You can use,
CONVERT_TZ(current_timestamp,'+00:00','-04:00').
and if you want to format the date you may use:
DATE_FORMAT(CONVERT_TZ(current_timestamp,'+00:00','-04:00'),'%Y-%m-%d %H:%i:%s')
PFA
Thanks,
06/02/2023 05:47 AM
Hi @sudeshjaiswal ,
Thanks for your response. The syntax which you suggested looks like MYSQL syntax is not working it says invalid identifier error and I'm inserting this into Oracle Database maybe that's why it's not accepting.
Regards,
Gopi.
06/02/2023 06:19 AM
Have you tried below?
CAST(FROM_TZ(CAST(DATE_FIELD AS TIMESTAMP), 'UTC') at time zone 'America/New_York' AS Date) as DESIRED_FIELD_NAME
06/04/2023 08:31 PM
Please refer https://community.oracle.com/customerconnect/discussion/614383/converting-utc-to-est-time-zone-in-lo...