DATE FUNCATION IN MYSQL

 PRINT CURRENT DATE  

 (i)   MYSQL> select  now ();

        print today date : 2020-08-16

 (ii)  MYSQL> select  CURDATE();

        print today date : 2020-08-16

PRINT YESTERDAY DATE

(i)  MYSQL> select SUBDATE(CURDATE(),1)

      print today date : 2020-08-15

Example :

Calculate days :-

SELECT DATEDIFF("2020-06-15","2019-06-15");

Calculate date :  '366' days

 

Example :

SET @v_start_date=ADDDATE(DATE(start_date),INTERVAL  -930 minute);
 

SET @v_end_date=ADDDATE(DATE(end_date),INTERVAL -940 minute);
 

select  @v_start_date ;                  #1days before 08:30 hours                        select  @v_end_date ;                    #today before 08:20 hours

 select * from student where datadate between @v_start_date  and        @v_end_date ;

PRINT OUTPUT:- select data from 1day before and till today date.

 

ADD DAY IN DATE 

  MYSQL> SELECT DATE_ADD("2020-08-15", INTERVAL 10 DAY);  

  print today date : 2020-08-25

 ADD MINUTE IN DATE TIME 

  MYSQL>SELECT DATE_ADD("2020-08-15 09:34:21", INTERVAL 15 MINUTE);

  print today date :  2020-08-15 09:49:21

 

SUBTRACT 3 HOURS IN TO DATE

    MYSQL>SELECT DATE_ADD("2020-08-15 09:34:21", INTERVAL -3 HOUR);

                                           

     print today date :  2020-08-15 06:34:21

 

 SUBTRACT 2 MONTHS IN TO DATE

    MYSQL>SELECT DATE_ADD("2020-08-15", INTERVAL -2 MONTH); 

    print today date :  2017-04-15

 DATE FORMAT  : Fetch Year  

SET  @datadate='2020-06-16'

Select DATE_FORMAT(@datadate, "%Y");

                                                                                       Output : 2020

SET  @datadate='2020-06-16'

SELECT DATE_FORMAT(@datadate, "%M %d %Y");   Output : June 16 2020

 

SET  @datadate='2020-06-16'

 

SELECT DATE_FORMAT("2017-06-15", "%W %M %e %Y");  

                                                                                    Output: Thursday June 15 2017
 

Fetch Hour Minute Second using Format:

 

SELECT DATE_FORMAT("2020-06-10 23:50:50", "%H:%i:%s") FROM Employees;

 

                                                                                                   Output:    23:50:50

Fetch  Week in Month: 

SELECT DAYOFWEEK("2020-08-15");                                   Output: 7

GET DAY IN YEAR

SELECT DAYOFYEAR("2020-06-15");                                    Output: 167

EXTRACT

SELECT EXTRACT(YEAR FROM "2020-06-15");                Output : 2020

SELECT EXTRACT(MONTH FROM "2020-06-15");           Output :  06

SELECT EXTRACT(DAY FROM "2020-06-15");                   Output :  15