DB Oracle_Datetime

 

Date-Time Conversion:

## Convert Timestamp to date like 9/11/2021 10:11:10 AM -> 09/11/2021
SELECT TO_CHAR(TO_DATE('9/11/2021 10:11:10 AM', 'DD/MM/YYYY HH:MI:SS PM'), 'DD/MM/YYYY') FROM DUAL;

## Convert  9/11/2021 10:11:10 AM -> 09/nov/2021
SELECT TO_CHAR(TO_DATE('9/11/2021 10:11:10 AM', 'DD/MM/YYYY HH:MI:SS PM'), 'DD/mon/YYYY') FROM DUAL;

## Add/substract no of days like ("11/15/2021 4:58:35.596542 AM -06:00", "11/16/2021 4:58:35.596542000 AM -06:00")
>>  SELECT SYSTIMESTAMP, SYSTIMESTAMP+NUMTODSINTERVAL(1,'DAY') AS DAYS_ADD FROM DUAL;

>> date_add(now(),interval -6 hour)

>> select CURRENT_TIMESTAMP - INTERVAL '10' DAY from dual;

## Convert Date string into format like '20210519' -> '2021-05-19' (String Data-type)
SELECT TO_CHAR(TO_DATE('20210519','YYYYMMDD'), 'YYYY-MM-DD') as PERIOD_NAME FROM DUAL;

## Convert Date string into format like '20210519' -> 5/19/2021 (Date data-type)
SELECT TO_DATE('20210519', 'YYYY-MM-DD') as PERIOD_NAME FROM DUAL;