Date Functions in sql

Find Age in days:

SQL> select to_date(sysdate) - to_date ( '07-Nov-2013') Age_in_days from dual;

AGE_IN_DAYS

-----------

       3183

Age in Months:   

SQL> select MONTHS_BETWEEN (sysdate,'07-Nov-2013') Age_in_Months from dual;

AGE_IN_MONTHS

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

   104.619876

Age in Years:

SQL> select MONTHS_BETWEEN (sysdate,'07-Nov-2013') /12 Age_in_Year from dual;

AGE_IN_YEAR

-----------

 8.71832353

Day from date:

SQL> select  TO_CHAR( sysdate, 'DAY' ) from dual;

TO_CHAR(SYSDATE,'DAY')

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

TUESDAY

Date format:

SQL> select  TO_CHAR( sysdate, 'DL' ) from dual;

TO_CHAR(SYSDATE,'DL')

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

Tuesday, July 26, 2022

Day of week (1-7):

SQL> select  TO_CHAR( sysdate, 'D' ) from dual; 

T

-

3

Name of day:

SQL> select  TO_CHAR( sysdate, 'DAY' ) from dual; 

TO_CHAR(SYSDATE,'DAY')

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

TUESDAY

Name of Day:

SQL> select  TO_CHAR( sysdate, 'DY' ) from dual;

TO_CHAR(SYSD

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

TUE

Day of month (1-31):

SQL> select  TO_CHAR( sysdate, 'DD' ) from dual;

TO

--

26

SQL> select to_char(sysdate,'DDth') day from dual;

DAY

----

26TH

SQL> select to_char(sysdate,'Ddspth') from dual;

TO_CHAR(SYSDAT

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

Twenty-Sixth

Day of year (1-366):

SQL> select  TO_CHAR( sysdate, 'DDD' ) from dual;

TO_

---

207

Year format:

SQL> select  TO_CHAR( sysdate, 'YEAR') from dual;  

TO_CHAR(SYSDATE,'YEAR')

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

TWENTY TWENTY-TWO

SQL> select  TO_CHAR( sysdate, 'YYYY') year from dual;

YEAR

----

2022

SQL> select  TO_CHAR( sysdate, 'YYY') year from dual;

yea

---

022

SQL> select  TO_CHAR( sysdate, 'YY') year from dual;

ye

--

22

SQL> select  TO_CHAR( sysdate, 'Y')  year  from dual;

y

-

2

Month Format:

SQL> select  TO_CHAR( sysdate, 'MONTH') from dual;

TO_CHAR(SYSDATE,'MONTH')

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

JULY

SQL> select  TO_CHAR( sysdate, 'MON') from dual;

TO_CHAR(SYSD

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

JUL

SQL> select  TO_CHAR( sysdate, 'MM') from dual;

TO

--

07

Few more formats based on date: 

SQL> select to_char(sysdate,'DDth Month ') from dual;

TO_CHAR(SYSDATE,'DDTHMONTH')

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

26TH July

SQL> select to_char(sysdate,'DDth Month Day') from dual;

TO_CHAR(SYSDATE,'DDTHMONTHDAY')

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

26TH July      Tuesday

SQL> select to_char(sysdate,'DDth Month Day Year') from dual;

TO_CHAR(SYSDATE,'DDTHMONTHDAYYEAR')

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

26TH July      Tuesday   Twenty Twenty-Two

SQL> select to_char(sysdate,'Day DDth Month YYYY') from dual;

TO_CHAR(SYSDATE,'DAYDDTHMONTHYYYY')

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

Tuesday   26TH July      2022

SQL> select to_char(sysdate,'YYYY Month DDth') from dual;

TO_CHAR(SYSDATE,'YYYYMONTHDDTH')

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

2022 July      26TH

next_day:

Find next Saturday or any day:

SQL> select next_day(sysdate,'SATURDAY') Next_Saturday from dual;

NEXT_SATURDAY

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

30-Jul-2022

SQL> select next_day('07-Nov-2013','THURSDAY') from dual;

NEXT_DAY('07-NOV-201

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

14-Nov-2013

First Monday of Current Month:

SQL>  select next_day(trunc(sysdate, 'mm'), 'monday') first_monday    from dual;

FIRST_MONDAY

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

04-Jul-2022

First Monday of Previous Month:

SQL> select next_day(add_months(trunc(sysdate, 'mm'), -1), 'monday') first_monday    from dual;

FIRST_MONDAY

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

06-Jun-2022

First Monday of Next Month:

SQL> select next_day(add_months(trunc(sysdate, 'mm'), +1), 'monday') first_monday    from dual;

FIRST_MONDAY

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

08-Aug-2022

first week day of current month:

SQL> select  TO_CHAR(trunc(sysdate, 'mm'),'DAY') first_day_ofMonth    from dual;

FIRST_DAY_OFMONTH

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

FRIDAY

Find First day of month for next n months:

SQL>  select  TO_CHAR(add_months(trunc(sysdate, 'mm'), level-1),'MON YYYY') Month_Year,TO_CHAR(add_months(trunc(sysdate, 'mm'), level-1),'DAY') First_day_of_Month from  dual  connect by level <= 12;

MONTH_YEAR        FIRST_DAY_OF_MONTH

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

JUL 2022          FRIDAY

AUG 2022          MONDAY

SEP 2022          THURSDAY

OCT 2022          SATURDAY

NOV 2022          TUESDAY

DEC 2022          THURSDAY

JAN 2023          SUNDAY

FEB 2023          WEDNESDAY

MAR 2023          WEDNESDAY

APR 2023          SATURDAY

MAY 2023          MONDAY

JUN 2023          THURSDAY

12 rows selected.

Date format at session level:

alter session set nls_date_format = 'DD-Mon-YYYY';

alter session set nls_timestamp_format = 'DD-Mon-YYYY hh24:mi:ss';