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
1 comment:
select sysdate -1/24 "1_Hr", sysdate-10/24/60 "10_Min" ,sysdate from dual ;
1_Hr 10_Min SYSDATE
---------------- ---------------- ----------------
28-12-2017 10:56 28-12-2017 11:46 28-12-2017 11:56
==============
For before 10 min RMAN recovery
set until time "SYSDATE -10/24/60" ;
Post a Comment