MySql Date Function and Query Example

MySql Date() Function List for Your Reference with Query Example. Consider Below table for Executing all the MySql Date Function.

Consider Below table Named with  Personal_Detail

FULLNAME	DOB	CREATED_AT
MUNISHA D	31-10-1996	14-11-2019 09:43
ANANTHA KUMAR C	20-08-2008	14-11-2019 10:12
THIRUMALAIKUMAR	28-06-2003	14-11-2019 09:53
DHANALAXSHMI S	10-06-2004	14-11-2019 10:11
AGNES LIDIA A	21-04-2003	14-11-2019 00:14
MOHANASRI G	28-05-2004	14-11-2019 10:12
PRIYANKA P	12-05-2001	14-11-2019 09:44
RANJITHKUMAR R	02-07-2004	14-11-2019 10:11
SABESH S	03-06-2005	14-11-2019 09:54
AMSAVALLI E	07-06-2004	14-11-2019 10:02

We will Write query to extract the data using MySql Date() function. Kindly go through the basic and view the query in the last for enhancing your SQL Skills.

MySql Date() Function with Example

---

⧉   CURDATE()  MySql Function : Once the below query will run it will O/P The current Date.

  SELECT CURDATE()    

   O/P : 2020-04-29  

---

⧉   CURTIME()  MySql Function :  Below Query will give you the Current time detail whatever time it is. Time will be in "HH-MM-SS" (string Format)

    SELECT CURDATE()    

    O/P : 12.52.09 

---

MySql Current TimeStamp Function 

---

⧉   CURRENT_TIMESTAMP()  MySql Function :  CURRENT_TIMESTAMP will return the Date with time

    SELECT CURRENT_TIMESTAMP()    

    O/P : 2020-04-29 19:44:07

---

⧉   DATE()  MySql Function :  DATE() function will be used to compare the date or to extract the date.

      SELECT  DATE("2020-04-29  06:34:21")

      O/P : 2020-04-29 

---

⧉   MONTH()  MySql Function :  MONTH() function will be used to extract the month from the given Date.

      SELECT  MONTH("2020-04-29  06:34:21")

      O/P : 4 

---

⧉   DAY()  MySql Function :  DAY() function will be used to extract the DAY from the given Date.

      SELECT  DAY("2020-04-29  06:34:21")

      O/P : 29 

---

⧉   DATEDIFF()  MySql Function :  DAYDIFF() function will compare the two date and return the number of day. Syntax : DATEDIFF(dateSel1, dateSel2)

      ⇒ SELECT  DATEDIFF("2020-10-29", "2020-04-29")

      O/P : 183

      ⇒ SELECT DATEDIFF( "2021-10-29" , CURDATE() )

      O/P : 548

       ⇒ SELECT DATEDIFF( "2021-07-29  09:34:21" , CURRENT_TIMESTAMP() )

      O/P : 456

---

⧉   DATE_ADD()  MySql Function :  DATE_ADD() function will add DAY, TIME, MONTH in the given date. Syntax : DATE_ADD(dateSel1, INTERVAL value  UnitAdd). UnitAdd can be DAY, WEEK, MONTH, YEAR, SECOND, MINUTE, HOUR etc..

      ⇒ SELECT  DATE_ADD("2020-10-29", INTERVAL  10  DAY )

      O/P : 2020-11-08

      ⇒ SELECT  DATE_ADD("2020-10-29", INTERVAL  10  MONTH )

      O/P : 2021-08-29

      ⇒ SELECT  DATE_ADD( CURRENT_TIMESTAMP() , INTERVAL  10  MONTH )

      O/P : 2021-02-28 20:08:34

---

⧉   DATE_SUB()  MySql Function :  DATE_SUB() function will substract DAY, TIME, MONTH in the given date. Syntax : DATE_SUB(dateSel1, INTERVAL value  UnitSub). UnitSub can be DAY, WEEK, MONTH, YEAR, SECOND, MINUTE, HOUR etc..

      ⇒ SELECT  DATE_SUB("2020-10-29", INTERVAL  10  DAY )

      O/P : 2020-10-19

      ⇒ SELECT  DATE_SUB("2020-10-29", INTERVAL  10  MONTH )

      O/P : 2019-12-29

      ⇒ SELECT  DATE_SUB( CURRENT_TIMESTAMP() , INTERVAL  10  MONTH )

      O/P : 2019-06-29 20:12:43

---

⧉   DATE_FORMAT()  MySql Function :  Know Basic Prefix for DATE_FORMAT()

• "%D" : It will return the day in numeric form with suffix like 1st, 2nd, 3rd, etc.  
• "%d"  : It will return the numeric value without suffix like 1, 2, 3, 4,..
• "%M" : It will return the month name fully.
• "%m" : It will return the month in numeric form.
• "%Y" : It will return the full year in four digit 2020, 2019, 2018 etc..
• "%y"  : It will return the year in two digit 20, 19, 18, 17 etc..

     
      ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%D" )
      O/P : 29th

      ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%d" )
      O/P : 29

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%M" )
      O/P : April

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%m" )
      O/P : 04

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y" )
      O/P : 2020

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%y" )
      O/P : 20

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y  %M" )
      O/P : 2020 April

       ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y  %M %D" )
      O/P : 2020 April 29th

      ⇒ SELECT  DATE_FORMAT( CURRENT_TIMESTAMP() ,  "%Y /%m/%d" )
      O/P : 2020/04/29

---

⧉   NOW()  MySql Function :  It will return the current time and stamp in the format of "YYYY-MM-DD HH.MM.SS". NOW() Function and CURRENT_TIMESTAMP() will return the same value. 

  ⇒ SELECT NOW( )

      O/P : 2020-04-29 22:09:57