DATE Function

 

Current Date & Time

Function	Description
SYSDATE	Returns current date and time of the DB server
CURRENT_DATE	Returns current date & time in user's session time zone
SYSTIMESTAMP	Returns current timestamp with time zone info

select sysdate, current_date,SYSTIMESTAMP from dual; 

• 23-06-25 23-06-25 23-06-25 4:29:58.304000000 PM +05:30
• 
  

====================================================================

Date Conversion Functions

Function	Description
TO_DATE(string, format)	Converts string to DATE
TO_CHAR(date, format)	Converts date to a formatted string
TO_TIMESTAMP(string, format)	Converts string to TIMESTAMP

SELECT 

  emp_bod, 

  TO_CHAR(emp_bod, 'YYYY-MM-DD') AS formatted_date1,

  TO_CHAR(emp_bod, 'YEAR-MON-DAY') AS formatted_date2,

  TO_CHAR(emp_bod, 'DD-MON-YY') AS formatted_date3,

  TO_DATE(TO_CHAR(emp_bod, 'YYYY-MM-DD'), 'YYYY-MM-DD') AS reconverted_date

FROM emp1;

15-07-83 1983-07-15 NINETEEN EIGHTY-THREE-JUL-FRIDAY    15-JUL-83 15-07-83

25-07-96 1996-07-25 NINETEEN NINETY-SIX-JUL-THURSDAY 25-JUL-96 25-07-96

====================================================================

 Date Arithmetic

Operation	Description
date + N	Adds N days
date - N	Subtracts N days
MONTHS_BETWEEN(d1, d2)	Number of months between 2 dates
ADD_MONTHS(date, N)	Add N months to a date
NEXT_DAY(date, 'MONDAY')	Next specified weekday
LAST_DAY(date)	Last day of the month

SELECT 

  emp_bod,EMP_JOININGDATE, 

  emp_bod+7 as , 

  emp_bod-10, 

  add_months(emp_bod, 2),

   to_char(emp_bod, 'day'),

  next_day(emp_bod, 'monday'),

    to_char(emp_bod, 'dy'),

    last_day(emp_bod),

    to_char( last_day(emp_bod),'month'),

  months_between(emp_bod, EMP_JOININGDATE)

FROM emp1;

25-07-96     25-07-06 01-08-96     15-07-96 25-09-96     thursday 29-07-96     thu     31-07-96 july      -120

====================================================================

EXTRACT Components

Function	Description
EXTRACT(YEAR FROM date)	Gets the year part
EXTRACT(MONTH FROM date)	Gets the month
EXTRACT(DAY FROM date)	Gets the day

select emp_bod, 

        EXTRACT(year from emp_bod),

        EXTRACT(MONTH from emp_bod),

        EXTRACT(DAY from emp_bod) from emp1;

• 15-07-83 1983 7 15
• 25-07-96 1996 7 25

=============================================================================

Date Truncation and Rounding

Function	Description
TRUNC(date, 'format')	Truncates time or to month/year
ROUND(date, 'format')	Rounds to nearest date unit

SELECT sysdate,

    TRUNC(SYSDATE, 'MONTH') AS first_day_of_month,

   ROUND(SYSDATE, 'YEAR') AS rounded_year 

FROM dual;

• 23-06-25 01-06-25 01-01-25

SELECT 

  emp_bod, TRUNC((emp_bod+7), 'month') ,

 EMP_JOININGDATE,  round(EMP_JOININGDATE,'year')

 FROM emp1;

• 15-07-83 01-07-83 25-12-12 01-01-13
• 25-07-96 01-08-96 25-07-06 01-01-07