Get Latest Updates directly delivered in your Email

Manipulation with Date Functions in MySQL

1
Publish On In Date and Time, MySQL

Like Others RDBMS , MySQL contains huge ranges of Date and Time library Functions. Using this library function you can do almost everything on Date and Time Manipulation whatever your needed and requirements. From my past experience i had seen there are lots of developer who uses php library function inside of MySQL Query , that is not incorrect. But you can arise a small question in your mind why should i use php function inside of MySQL Statements, if MySQL has same kind library functions and returns same kind of ouputs.

In this article i will discuss about most happening MySQL Date and Time Library functions.

Get Previous Date in MySQL

SELECT
  /** Get Previous Date **/
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) AS getPreviousDate

Results

+-----------------+
| getPreviousDate |
+-----------------+
| 2012-05-12      |
+-----------------+
1 row in set (0.00 sec)

Get Todays Date in MySQL

SELECT
  /** Get Todays Date **/
  DATE_FORMAT(NOW(),'%Y-%m-%d') AS getTodaysDate,
  /** Get Todate Date Using CURDATE FUNCTION **/
  CURDATE() AS getTodaysDateUsingCURDATE

Output

+---------------+---------------------------+
| getTodaysDate | getTodaysDateUsingCURDATE |
+---------------+---------------------------+
| 2012-05-13    | 2012-05-13                |
+---------------+---------------------------+
1 row in set (0.00 sec)

Add 7 Days From Todays Date

SELECT
/** Add 7 Days From Todays Date **/
  DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) AS add7DaysFromTodaysDate

Output

+------------------------+
| add7DaysFromTodaysDate |
+------------------------+
| 2012-05-20             |
+------------------------+
1 row in set (0.00 sec)

Substract 7 Days From Todays Date

SELECT
/** Substract 7 Days From Todays Date **/
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) AS substract7DaysFromTodaysDate

Results

+------------------------------+
| substract7DaysFromTodaysDate |
+------------------------------+
| 2012-05-06                   |
+------------------------------+
1 row in set (0.00 sec)

Add One Month From Todays Date

SELECT
/** Add 1 Month From Todays Date **/
  DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 MONTH) AS add1MonthFromTodaysDate

Output

+-------------------------+
| add1MonthFromTodaysDate |
+-------------------------+
| 2012-06-13              |
+-------------------------+
1 row in set (0.00 sec)

Substract 1 Month From Todays Date

SELECT
/** Substract 1 Month From Todays Date **/
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 MONTH) AS substract1MonthFromTodaysDate

Result

+-------------------------------+
| substract1MonthFromTodaysDate |
+-------------------------------+
| 2012-04-13                    |
+-------------------------------+
1 row in set (0.00 sec)

Get 1st Date of current Month

SELECT
 /** Get 1st Date of current Month **/
  DATE_FORMAT(NOW(),'%Y-%m-01') AS getFirstDayCurrentMonth

+-------------------------+
| getFirstDayCurrentMonth |
+-------------------------+
| 2012-05-01              |
+-------------------------+
1 row in set (0.00 sec)
Here is another way to get first day of current month 
SELECT
/** Another Way to get 1st Date of Current Month **/
  DATE_FORMAT(DATE_SUB(now(),INTERVAL (DAY(now())-1) DAY),'%Y-%m-%d') AS getFirstDayCurrentMonth_1

Get Last Date of MySQL Using LAST_DATE Function

SELECT
/** Get Last Date of MySQL Using LAST_DATE Function **/
  LAST_DAY(DATE_FORMAT(NOW(),'%Y-%m-%d')) AS lastDayCurrentMonth

Output

+---------------------+
| lastDayCurrentMonth |
+---------------------+
| 2012-05-31          |
+---------------------+
1 row in set (0.00 sec)

Get First Date For Previous Month

SELECT
/** Get First Date For Previous Month **/
  DATE_SUB(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)),
  INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS getFirstDateForPreviousMonth

Output

+------------------------------+
| getFirstDateForPreviousMonth |
+------------------------------+
| 2012-04-01                   |
+------------------------------+
1 row in set (0.00 sec)

Get Last Date For Previous Month

SELECT
 /** Get Last Date For Previous Month **/
  LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS getLastDateForPreviousMonth

Output

+-----------------------------+
| getLastDateForPreviousMonth |
+-----------------------------+
| 2012-04-30                  |
+-----------------------------+
1 row in set (0.00 sec)

Get First Date for Next Month

SELECT
 /** Get First Date for Next Month **/
  DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)),
  INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS getFirstDateNextMonth

Result

+-----------------------+
| getFirstDateNextMonth |
+-----------------------+
| 2012-06-01            |
+-----------------------+
1 row in set (0.00 sec)

Get Last Date Next Month 

SELECT
/** Get Last Date Next Month **/
  LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)) AS getLastDateForNextMonth

Output

+-------------------------+
| getLastDateForNextMonth |
+-------------------------+
| 2012-06-30              |
+-------------------------+
1 row in set (0.00 sec)

So Wrap it All 

SELECT
  /** Get Previous Date **/
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 DAY) AS getPreviousDate,
  /** Get Todays Date **/
  DATE_FORMAT(NOW(),'%Y-%m-%d') AS getTodaysDate,
  /** Get Todate Date Using CURDATE FUNCTION **/
  CURDATE() AS getTodaysDateUsingCURDATE,
  /** Add 7 Days From Todays Date **/
  DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) AS add7DaysFromTodaysDate,
  /** Substract 7 Days From Todays Date **/
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 7 DAY) AS substract7DaysFromTodaysDate,
  /** Add 1 Month From Todays Date **/
  DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 MONTH) AS add1MonthFromTodaysDate,
  /** Substract 1 Month From Todays Date **/
  DATE_SUB(DATE_FORMAT(NOW(),'%Y-%m-%d'),INTERVAL 1 MONTH) AS substract1MonthFromTodaysDate,
  /** Get 1st Date of current Month **/
  DATE_FORMAT(NOW(),'%Y-%m-01') AS getFirstDayCurrentMonth ,
  /** Another Way to get 1st Date of Current Month **/
  DATE_FORMAT(DATE_SUB(now(),INTERVAL (DAY(now())-1) DAY),'%Y-%m-%d') AS getFirstDayCurrentMonth_1,
  /** Get Last Date of MySQL Using LAST_DATE Function **/
  LAST_DAY(DATE_FORMAT(NOW(),'%Y-%m-%d')) AS lastDayCurrentMonth,
  /** Get First Date For Previous Month **/
  DATE_SUB(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)),
            INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS getFirstDateForPreviousMonth,
  /** Get Last Date For Previous Month **/
  LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS getLastDateForPreviousMonth,
  /** Get First Date for Next Month **/
  DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)),
            INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS getFirstDateNextMonth,
  /** Get Last Date Next Month **/
  LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)) AS getLastDateForNextMonth
For further reading Please refer to MySQL Documentation Library For Date and Time Functions

Please give your valuable suggestions and improve this article if you think so..

Related Article you might like:
Next:
PeopleCode Examples for Traversing Level 0 to Level 3

One comment on “Manipulation with Date Functions in MySQL”

Leave a Reply

Your email address will not be published. Required fields are marked *

Show Comment Guide
Write your code inside of code or pre tag

<code> your code resides here </code>