MySQL Tips on Date

While we work on some custom php or mysql projects then often we require to make transaction or actions reports based on dates and date range. Like Daily, yesterday’s, last seven day’s, last months, last 3 month’s etc. We can do that easily with sql queries describe in todays MySQL Tips on Date

MySQL Tips on Date

For Example: If we want to get all the orders which was placed yesterday,

order_placed_date = date_format(NOW() - INTERVAL 1 DAY, '%Y-%m-%d')

If we want to get all the orders which was placed last seven day:

order_placed_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 7 Day, '%Y-%m-01')
AND DATE_FORMAT(NOW() ,'%Y-%m-%d')

If we want to get all the orders which was placed last month:

order_placed_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01') AND DATE_FORMAT(NOW() ,'%Y-%m-01')
Similarly for last 3 month it will be like this:

order_placed_date BETWEEN DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y-%m-01') AND DATE_FORMAT(NOW() ,'%Y-%m-01')

So we hope these MySQL tips on date will help you in your projects. 

Related Posts