date string to date¶

SELECT  TO_DATE('3/1/2023 11:31:24 am', 'DD/MM/YYYY HH:MI:SS AM') FROM DUAL;

select 1 from dual where
12 BETWEEN 18 AND 19

get next sunday¶

SELECT NEXT_DAY( DATE '2022-11-30', 'SUNDAY' ) --  can be in short form: 'sun'
FROM DUAL
UNION ALL
SELECT NEXT_DAY( DATE '2022-11-30', 'MONDAY' ) -- 'MON'
FROM DUAL
UNION ALL
SELECT NEXT_DAY( DATE '2022-11-30', 'TUESDAY' ) -- 'TUE'
FROM DUAL
UNION ALL
SELECT NEXT_DAY( DATE '2022-11-30', 'WEDNESDAY' ) -- 'WED'
FROM DUAL
UNION ALL
SELECT NEXT_DAY( DATE '2022-11-30', 'THURSDAY' ) -- 'THU'
FROM DUAL
UNION ALL
SELECT NEXT_DAY( DATE '2022-11-30', 'FRIDAY' ) -- 'FRI'
FROM DUAL
UNION ALL
SELECT NEXT_DAY( DATE '2022-11-30', 'SATURDAY' ) -- 'SAT'
FROM DUAL

to string¶

SELECT TO_CHAR( sysdate, 'YYYYMMDD' )
FROM dual;

SELECT TO_CHAR( sysdate, 'YYYY-MM-DD HH24:MI:SS' )
FROM dual;

SELECT TO_CHAR( sysdate, 'YYYY-MM-DD HH:MI:SS AM' ) -- can be am or pm
FROM dual;

to date¶

SELECT TO_DATE('20030709', 'yyyyMMdd') FROM DUAL;

last date¶

SELECT
  LAST_DAY(TO_DATE('200307', 'yyyyMM'))
FROM
  DUAL;

first date of month¶

SELECT
    trunc(sysdate, 'year')
FROM
  DUAL;

last year last date¶

SELECT TO_CHAR (TRUNC (TO_DATE ('20221030', 'YYYYMMDD'), 'YEAR') - 1,  'YYYYMMDD' ) FROM DUAL;

oracle/set date format, oracle nls

get previous year¶

select
TO_CHAR (TRUNC (TO_DATE ('20221030', 'YYYYMMDD'), 'YEAR' ) - 1 - interval '1' year , --for year 2021
'YYYYMMDD')
from dual;

Interval¶

select interval '1' day from dual;