SQL Date Data Type - Tutorial using Oracle

Create the employee table

 
SQL> create table emp(id number,joindate date,retiredate date);


Output
Table created.

Describe the table

 
SQL> desc emp;

Output
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                                                 NUMBER
JOINDATE                                           DATE
RETIREDATE                                         DATE

Insert a record in to the emp table

 
SQL> insert into emp values(123,'21-sep-90','27-oct-20');

Output
1 row created.

View the records in the emp table

 
SQL> select * from emp;

Output
       ID JOINDATE  RETIREDAT
---------- --------- ---------
       123 21-SEP-90 27-OCT-20

Sql query to view the current date

 
SQL> select sysdate from dual;

Output
SYSDATE
---------
09-AUG-23

Substracting 10 days from the current date and displaying the date

 
SQL> select sysdate-10 from dual;

Output
SYSDATE-1
---------
30-JUL-23

Adding 1 day to the current date and displaying the date

 
SQL> select sysdate+1 from dual;

Output
SYSDATE+1
---------
10-AUG-23

Extracting the month from the the joindate in emp table

 
SQL> select extract(month from joindate) from emp;

Output
EXTRACT(MONTHFROMJOINDATE)
--------------------------
                         9

Extracting the day from the the joindate in emp table

 
SQL> select extract(day from joindate) from emp;

Output
EXTRACT(DAYFROMJOINDATE)
------------------------
                      21

Extracting the year from the the joindate in emp table

 


Output

 
SQL> select extract(year from joindate) from emp;

Output
EXTRACT(YEARFROMJOINDATE)
  -------------------------
                       1990

Finding the months between two dates

 
SQL> select months_between(sysdate,joindate) from emp;

Output
MONTHS_BETWEEN(SYSDATE,JOINDATE)
--------------------------------
                        394.6307

Finding the years between two dates

 
SQL> select round(months_between(sysdate,joindate)/12) from emp;

Output
ROUND(MONTHS_BETWEEN(SYSDATE,JOINDATE)/12)
------------------------------------------
                                        33

Using add_months to add 3 months to the current date

 
SQL> select add_months(sysdate,3) from dual;

Output
ADD_MONTH
---------
09-NOV-23

Displaying the last_day of the current month

 
SQL> select last_day(sysdate) from dual;

Output
LAST_DAY(
---------
31-AUG-23

Displaying the first day of the current month

 
SQL> select trunc(sysdate,'month') from dual;

Output
TRUNC(SYS
---------
01-AUG-23

using to_char method to display date

 
SQL> select to_char(joindate,'dd month year') from emp;

Output
TO_CHAR(JOINDATE,'DDMONTHYEAR')
-------------------------------------------------------
21 september nineteen ninety

using to_char method to display date

 
SQL> select to_char(joindate,'dd mon year') from emp;

Output
TO_CHAR(JOINDATE,'DDMONYEAR')
-------------------------------------------------
21 sep nineteen ninety

using to_char method to display date with hours minutes and seconds

 
SQL> select to_char(joindate,'dd month yyyy HH MM SS') from emp;

Output
TO_CHAR(JOINDATE,'DDMONTHY)
--------------------------
21 september 1990 12 09 00

Insert a record into the emp table

 
SQL> insert into emp values(124,'24-jan-49','24-jan-51');

Output
1 row created.

Viewing the record from the emp table using to_char methods

year 49 is inserted as 2049 where as year 51 is inserted as 1951

 
SQL> select to_char(joindate,'dd month yyyy'),to_char(retiredate,'dd month yyyy') from emp where id=124;

Output
TO_CHAR(JOINDATE, TO_CHAR(RETIREDAT
----------------- -----------------
24 january   2049 24 january   1951

If you need to insert the years greater than 2050 follow the full year format as shown below

 
SQL> insert into emp values(125,'24-jan-2049','24-jan-2051');
  
1 row created.
  
SQL> select to_char(joindate,'dd month yyyy'),to_char(retiredate,'dd month yyyy') from emp where id=125;

Output
TO_CHAR(JOINDATE, TO_CHAR(RETIREDAT
----------------- -----------------
24 january   2049 24 january   2051