MySQL: Find data entered on this date one year ago

To find data entered into a MySQL database on this date one year ago you can use the MySQL date functions in your SQL query. The functions to use are:-


NOW – this gets the current date and time.
DATE – this extracts the date portion of a time value.
DATE_SUB – this subtracts an interval to a date/ time value.
DATE_ADD – this adds an internval to a date/ time value.


For example, if you have a field EntryDate in a table called Payments you can find all payment made on this day one year ago using a SQL query like this:


The parts of the query that look for EntryDate values between the start of the day one year ago and the start of tomorrow one year ago are:



This expression gets the date part of the time now using:



which gets the start of today, i.e. midnight, then subtracts 1 year using DATE_SUB. and then:



which adds 1 day to the start of today then subtracts one year.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>