[PL/SQL] Date Datatypes and Date Functions

Heyyy, What time is it 😎😎

Oracle has 3 date datatypes :

DATE : is the most popular type. It stores date and time (without timezone)

TIMESTAMP : same with DATE, but has higher precision and include timezone

INTERVAL : while DATE and TIMESTAMP store the current time, INTERVAL store and calculate time. You can specify the time period in years and months or days and seconds.

Quick Look

1.Get the current date and time

SELECT SYSDATE           --Database server DATE
      ,SYSTIMESTAMP      --Database server TIMESTAMP WITH TIME ZONE
      ,CURRENT_DATE      --Session DATE
      ,CURRENT_TIMESTAMP --Session TIMESTAMP WITH TIME ZONE
      ,LOCALTIMESTAMP    --Session TIMESTAMP         
  FROM dual;

2.Converting a date to string

SELECT TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
      ,TO_CHAR (SYSDATE,'Day DDth Month YYYY')
      ,TO_CHAR( SYSTIMESTAMP, 'TZH:TZM' ) timezone
      ,TO_CHAR (SYSDATE, 'Q')   --see a current quarter 
      ,TO_CHAR (SYSDATE, 'DDD') --date belongs to which year (1-366)
  FROM dual;

3.Converting a string to date

Use TO_DATE or TO_TIMESTAMP to convert a string to date

SELECT TO_DATE('20/02/2021','dd/mm/yyyy')
  FROM dual;

4.EXTRACT ()

To extract and return the value of a specified element of the date.

SELECT EXTRACT (YEAR FROM SYSDATE)
      ,EXTRACT (MONTH FROM SYSDATE)
      ,EXTRACT (DAY FROM SYSDATE)
  FROM dual;

5.TRUNC ()

• The most common way to use the TRUNC function is TRUNC (DATE): the time will be set to 00:00:00
• To truncate a date to the specified unit of measure.

SELECT TRUNC(SYSDATE)
      ,TRUNC(SYSDATE, 'MONTH') --Get the first day of the month
      ,TRUNC(SYSDATE, 'MM')    --Get the first day of the month
      ,TRUNC(SYSDATE, 'Q')     --Get the first day of the quarter
      ,TRUNC(SYSDATE, 'Y')     --Get the first day of the year
  FROM dual;

6.Additional functions

• ADD_MONTHS : adds the specified number of months to or subtracts it from a date (or a timestamp)
• NEXT_DAY : returns the date of the first weekday named in the call to the function
• LAST_DAY : returns the date of the last day of the month of the specified date

SELECT SYSDATE
      ,ADD_MONTHS (SYSDATE, 1)        --find this date next month
      ,ADD_MONTHS (SYSDATE, -3)       --find this date last 3 month
      ,NEXT_DAY (SYSDATE, 'SAT')      --find next Saturday
      ,NEXT_DAY (SYSDATE, 'SATURDAY') --find next Saturday
      ,LAST_DAY(SYSDATE)           --find the last day of the month
  FROM dual;