SQL Date

SQL> CREATE TABLE EMP(ID NUMBER,NAME VARCHAR2(15),DOJ DATE);

SQL> SELECT * FROM EMP;

        ID NAME            DOJ                                                 
---------- --------------- ---------                                           
         1 KAMLESH         07-MAY-15                                           
         2 RANJIT              11-DEC-11                                           
         3 RAJESH             11-FEB-01                                           
         4 SHIVAM            01-FEB-21                                           


SQL> SELECT * FROM EMP WHERE DOJ>SYSDATE;

        ID NAME            DOJ                                                 
---------- --------------- ---------                                           
         4 SHIVAM          01-FEB-21                                           

TO get next date

SQL> select * from emp;

        ID NAME            DOJ                                                 
---------- --------------- ---------                                           
         1 KAMLESH         07-MAY-15                                           
         2 RANJIT          11-DEC-11                                           
         3 RAJESH          11-FEB-01                                           
         4 SHIVAM          01-FEB-21                                           

SQL> select doj+1 from emp;

DOJ+1                                                                          
---------                                                                      
08-MAY-15                                                                      
12-DEC-11                                                                      
12-FEB-01                                                                      
02-FEB-21                                                                      

To get dob as date

SQL> create table demo(name varchar2(17), dob varchar2(15));

Table created.

SQL> select * from demo;

NAME              DOB                                                          
----------------- ---------------                                              
ravi                  11/12/1989                                                   
mohan             01/10/1980                                                   
suresh              03/09/1986                                                   


TO_DATE() : USE TO CONVERT STRING TYPE DATE VALUES TO DATE DATA TYPE.

SQL> select to_date(dob,'DD/MM/YYYY') from demo;

TO_DATE(D                                                                      
---------                                                                      
11-DEC-89                                                                      
01-OCT-80                                                                      
03-SEP-86                                                                      

SQL> select to_date(dob,'DD/MM/YYYY') AS DATE_OF_BIRTH from demo;

DATE_OF_B                                                                      
---------                                                                      
11-DEC-89                                                                      
01-OCT-80                                                                      
03-SEP-86                                                                      

TO_CHAR() : USE TO CONVERT OR CHANGE THE FORMAT OF DATE TYPE.


SQL> select TO_CHAR(to_date(dob,'DD/MM/YYYY'),'DD-MON-YEAR') AS DATE_OF_BIRTH from demo;

DATE_OF_BIRTH                                                                  
----------------------------------------------------------                     
11-DEC-NINETEEN EIGHTY-NINE                                                    
01-OCT-NINETEEN EIGHTY                                                         
03-SEP-NINETEEN EIGHTY-SIX                                                     

                                               

HOW TO FETCH MONTH FROM DATE

SQL> SELECT * FROM EMP;

        ID NAME            DOJ                                                 
---------- --------------- ---------                                           
         1 KAMLESH         07-MAY-15                                           
         2 RANJIT              11-DEC-11                                           
         3 RAJESH             11-FEB-01                                           
         4 SHIVAM            01-FEB-21                                           

HOW TO FETCH MONTH FROM DATE

SQL> SELECT TO_CHAR(DOJ,'MONTH') FROM EMP;

TO_CHAR(DOJ,'MONTH')                                                           
------------------------------------                                           
MAY                                                                            
DECEMBER                                                                       
FEBRUARY                                                                       
FEBRUARY                                                                       

HOW TO FETCH YEAR FROM DATE

SQL> SELECT TO_CHAR(DOJ,'YEAR') FROM EMP;

TO_CHAR(DOJ,'YEAR')                                                            
------------------------------------------                                     
TWENTY FIFTEEN                                                                 
TWENTY ELEVEN                                                                  
TWO THOUSAND ONE                                                               
TWENTY TWENTY-ONE                                                              

SQL> SELECT TO_CHAR(DOJ,'YYYY') FROM EMP;

TO_C                                                                           
----                                                                           
2015                                                                           
2011                                                                           
2001                                                                           
2021                                                                           

HOW TO FETCH DAY OF WEEK  FROM DATE

SQL> SELECT TO_CHAR(DOJ,'DAY') FROM EMP;

TO_CHAR(DOJ,'DAY')                                                             
------------------------------------                                           
THURSDAY                                                                       
SUNDAY                                                                         
SUNDAY                                                                         
MONDAY