Oracle Date Calculations .... 

Oracle Date's Maths with Anuj

Oracle Date Calculations ....

Oracle Date's Maths with Anuj

Oracle Date's Maths .....

Oracle Date Calculations ....

Oracle Date's Maths with Anuj
Oracle Date Calculations ....
Oracle Date's Maths with Anuj
Oracle Date's Maths .....
Oracle Date Calculations ....



select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') ssysdate ,TO_CHAR(sysdate -1,'DD-MM-YYYY HH24:MI:SS') "ssysdate-1" from dual;

SSYSDATE            ssysdate-1
------------------- -------------------
26-05-2012 16:45:20 25-05-2012 16:45:20



so " -1 " will subtract 1 day from sysdate .

suppose we want to subtract 1 minute , then we have to convert 1day into 1 minute

by dividing by ( 1day = 24 hr ,1hr = 60 min )


1/(24*60)

or

SQL> select 24*60 from dual;

24*60
----------
1440


SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS')     "ssysdate" ,
    TO_CHAR(sysdate -1,'DD-MM-YYYY HH24:MI:SS')          "SSYSDATE-1day",
    TO_CHAR(sysdate -1/(24*60),'DD-MM-YYYY HH24:MI:SS')  "SSYSDATE-1minute",
    TO_CHAR(sysdate -1/1440,'DD-MM-YYYY HH24:MI:SS')     "SSYSDATE-1minute",
    TO_CHAR(sysdate -10/1440,'DD-MM-YYYY HH24:MI:SS')    "SSYSDATE-10minute" 
    from dual; 

ssysdate            SSYSDATE-1day       SSYSDATE-1minute    SSYSDATE-1minute    SSYSDATE-10minute
------------------- ------------------- ------------------- ------------------- -------------------
26-05-2012 16:45:50 25-05-2012 16:45:50 26-05-2012 16:44:50 26-05-2012 16:44:50 26-05-2012 16:35:50




Subtract 1 Second 

SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH24:MI:SS') ssysdate,TO_CHAR(sysdate -1/(24*60*60),'DD-MM-YYYY HH24:MI:SS') "SSYSDATE-1 second" from dual; 


SSYSDATE            SSYSDATE-1 second
------------------- -------------------
26-05-2012 16:46:15 26-05-2012 16:46:14



select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') ssysdate ,to_char(sysdate + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from dual;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
26-may-2012 04:46:36 26-may-2012 05:46:36



 select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') ssysdate ,to_char(sysdate + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from dual;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
26-may-2012 04:47:07 26-may-2012 05:47:07




SQL> create table ddate ( x date );

Table created.

SQL> insert into ddate values ('1-jan-11') ;

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(x,'dd-mon-yyyy hh:mi:ss') ssysdate , to_char(x + INTERVAL '1' HOUR ,'dd-mon-yyyy hh:mi:ss') "ssysdate+1hr" from ddate;

SSYSDATE             ssysdate+1hr
-------------------- --------------------
01-jan-2011 12:00:00 01-jan-2011 01:00:00

 set linesize 300

 ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

 

 SELECT SYSDATE             as current_date,

       SYSDATE - 1          as minus_1_day,

       SYSDATE - 2/24       as minus_2_hours,

       SYSDATE - 10/24/60   as minus_10_minutes,

       SYSDATE - 1/24/60    as minus_1_minutes,

       SYSDATE  - 30/24/60/60 as minus_30_seconds

FROM   dual;

 

CURRENT_DATE         MINUS_1_DAY          MINUS_2_HOURS        MINUS_10_MINUTES     MINUS_1_MINUTES      MINUS_30_SECONDS

-------------------- -------------------- -------------------- -------------------- -------------------- --------------------

05-FEB-2021 07:14:10 04-FEB-2021 07:14:10 05-FEB-2021 05:14:10 05-FEB-2021 07:04:10 05-FEB-2021 07:13:10 05-FEB-2021 07:13:40

select sysdate , sysdate -1,sysdate -1.5 from dual;

SYSDATE          SYSDATE-1        SYSDATE-1.5

---------------- ---------------- ----------------

01-11-2024 11:14 31-10-2024 11:14 30-10-2024 23:14

===

 

 

 set linesize 300 

 SELECT 

   TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS A.M.') AS "Uppercase1"

   , TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS a.m.') AS "Lowercase1"

   , TO_CHAR(timestamp '2020-12-30 18:30:45', 'HH:MI:SS A.m.') AS "Mixed1"

  ,  TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS A.M.') AS "Uppercase"

  , TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS a.m.') AS "Lowercase"

 , TO_CHAR(timestamp '2020-12-30 18:30:45', 'yyyy-mm-dd HH:MI:SS A.m.') AS "Mixed"

FROM DUAL;

Uppercase1    Lowercase1    Mixed1        Uppercase                Lowercase                Mixed

------------- ------------- ------------- ------------------------ ------------------------ ------------------------

06:30:45 P.M. 06:30:45 p.m. 06:30:45 P.M. 2020-12-30 06:30:45 P.M. 2020-12-30 06:30:45 p.m. 2020-12-30 06:30:45 P.M.

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> 

select sysdate , sysdate-1/24,sysdate-.5/24,sysdate-.25/24 from dual;

SYSDATE             SYSDATE-1/24        SYSDATE-.5/24       SYSDATE-.25/24

------------------- ------------------- ------------------- -------------------

26-06-2023 05:34:38 26-06-2023 04:34:38 26-06-2023 05:04:38 26-06-2023 05:19:38

SQL>

-------------

select to_char(sysdate+n,'DAY','nls_date_language=ENGLISH') day

     from (select level n from dual connect by level <= 7)

    /

DAY

------------------------------------

WEDNESDAY

THURSDAY

FRIDAY

SATURDAY

SUNDAY

MONDAY

TUESDAY

****************

How to convert a normal integer value to DATE format?

Convert Oracle Timestamp To Epoch

select (cast(sys_extract_utc(current_timestamp) as date) - TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 as gmt_epoch

from dual;

var epoch number;

exec :epoch := 1234567890;

select /* Epoch to date */

       date'1970-01-01'   + ( :epoch / 86400 ) dt,

       /* Epoch to timestamp */

       timestamp'1970-01-01 00:00:00'    + numtodsinterval ( :epoch, 'second' ) DT 

   from   dual;

   

   with  second 

   TO_CHAR(

        TO_DATE('1970-01-01','YYYY-MM-DD') + numtodsinterval(value_string/ 1000,'SECOND'),'YYYY-MM-DD HH24:MI:SS'

       )

====

ORA-01849: hour must be between 1 and 12  >>>> should be hh24 in date format !!!

SQL> delete from T_INVOICE where INVOICE_date =to_date('16-11-2025 00:06:07','dd-mm-yyyy hh:mi:ss');

delete from T_INVOICE where INVOICE_date =to_date('16-11-2025 00:06:07','dd-mm-yyyy hh:mi:ss')

                                                  *

ERROR at line 1:

SQL> delete from T_INVOICE where INVOICE_date =to_date('16-11-2025 00:06:07','dd-mm-yyyy hh24:mi:ss');

delete from T_INVOICE where INVOICE_date =to_date('16-11-2025 00:06:07','dd-mm-yyyy hh24:mi:ss')

*

ERROR at line 1:

ORA-02292: integrity constraint (ANUJ1.FK_INVITEM_INVOICE) violated - child record found