Add 30 day to MySQL query

[Jayram121]

In the/ MySQL database date field stored the date in UK format (dd/mm/yyyy).

eg : 01/08/2013

 

In the query I would like to add 30 days to the data field and match it to the current date.

If it matches, show results.

 

For example, if the data field value 01/08/2013, when I query I will add 30 days in the php code then will become  31/08/2013. if the current date was 31/08/2013 this will match.

 

I am a bit confused on how I can do this, can you please give advice on how to do this

[Barand]

That date format is totally useless for databases. You can't compare date ranges, you can't sort and you can't make use of the dozens of dat/time functions without converting it first to the correcty YYYY-MM-DD format.

 

Use STR_TO_DATE() function to convert to DATE format then you can add INTERVAL 30 DAY to the result.

[objnoob]

SELECT .....
WHERE DATE_ADD(dateColumn, INTERVAL 30 DAY) = CURDATE();

or you can just match...................

dateColumn to 30 days ago.  which is better for those indices.

SELECT ....
WHERE dateColumn = DATE_SUB(CURDATE(), INTERVAL 30 DAY);

[jazzman1]

@objnoob, re-read the Barand's reply above.

These mysql date/time functions should work only if the date field is under date/time type in mysql!

Edited September 2, 2013 by jazzman1

[objnoob]

s/he should figure out sooner or later that CHAR(), VARCHAR() are not meant for dates.

anyhow, my main intention was to offer an efficient alternative solution.

[Jayram121]

It was great help. Thank You.

is it possible to convert it within the query

Edited September 2, 2013 by Jayram121

[objnoob]

You should do this............

 

ALTER TABLE your_table ADD COLUMN theDate DATE default null;      -- adds a column to the table that is of date datatype

UPDATE your_table SET theDate =  STR_TO_DATE(theUKDate);          -- updates the table setting the new column to the correct date in the correct format

ALTER TABLE your_table DROP COLUMN theUKDate;                           -- drops your column that is storing the date in the UK format which is useless

 

Now you're storing the date properly in the standard proper format.

When you need to query and wish to return the date in UK format you can use DATE_FORMAT function....

 

SELECT DATE_FORMAT(theDate, '%d/%m/%Y') as 'UK Date' FROM your_table;

[objnoob]

If you're going to be running a lot of data pulls based on the date, you would benefit tremendously by adding an index for the theDate column.

[Jayram121]

Thank you objnoob

[Barand]

... anyhow, my main intention was to offer an efficient alternative solution.

 

It's only efficient if it works, and date arithmetic will not work with d/m/y date formats

 

 

is it possible to convert it within the query

 

Yes.

... WHERE CURDATE() = STR_TO_DATE(ukDate, '%d/%m/%Y') + INTERVAL 30 DAY