Displaying Future Dates as Result

[manhattes]

HI freaks 

 

I have a database with information about various products. I would like to display the products that are coming out in the next 30 days.

 

The DB is currently configured as VARCHAR for the dates because it spells out the date ie wednesday, April 4th 2016

When I had it configured as DATE it was making everything 0000-00-00.

 

What is the SELECT statement i should use ? Or do I need to somehow convert the dates into DATE not VARCHAR?

 

I tried this but nothing is returned:

SELECT * FROM CleanedCalendar WHERE `Completion Date` BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW();

Edited July 31, 2015 by manhattes

[manhattes]

Or maybe something like this?

SELECT CleanedCalendar WHERE `Completion Date`( NOW( ) + INTERVAL 1 MONTH )

 

(Doesnt work

[mac_gyver]

one of the reasons for the yyyy-mm-dd format for a DATE data type is because that format is required in order to compare dates by magnitude. other reasons for using a DATE data type include being able to use the mysql DATE functions on the value, the most efficient data storage, and the quickest queries.

 

you need to store your dates as a DATE data type, with that format. to insert dates that have a different format into a DATE data type, you need to reformat them. you can either do this in your php code or you can use the mysql STR_TO_DATE() function in your query.

[manhattes]

one of the reasons for the yyyy-mm-dd format for a DATE data type is because that format is required in order to compare dates by magnitude. other reasons for using a DATE data type include being able to use the mysql DATE functions on the value, the most efficient data storage, and the quickest queries.

 

you need to store your dates as a DATE data type, with that format. to insert dates that have a different format into a DATE data type, you need to reformat them. you can either do this in your php code or you can use the mysql STR_TO_DATE() function in your query.

So what do I do if it is formatted like this using STR_TO_DATE: ?

Wednesday, November 01, 2006

[manhattes]

I am trying to do it in excel but it only applies the format to entries that are new. It copies the format that is wrong if i copy and paste.

[Barand]

you would convert the date like this

SELECT STR_TO_DATE('April 4th 2016', '%M %D %Y');  // --> 2016-04-04

So your query becomes this (you current query, BTW, searches the last 30 days, not the next 30)

SELECT * FROM CleanedCalendar 
WHERE STR_TO_DATE(Completion Date, '%M %D %Y') 
   BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY;

[manhattes]

 

you would convert the date like this

SELECT STR_TO_DATE('April 4th 2016', '%M %D %Y');  // --> 2016-04-04

So your query becomes this (you current query, BTW, searches the last 30 days, not the next 30)

SELECT * FROM CleanedCalendar 
WHERE STR_TO_DATE(Completion Date, '%M %D %Y') 
   BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY;

This returns zero results so it doesnt seem to be working.

Is there a way to convert it and save it to a new column that is DATE and not VARCHAR?

[Barand]

Of course there is a way to convert it - that's what STR_TO_DATE() is doing.

 

BTW, date column name will need the backticks - I just noticed you haven't got rid of the spaces as advised.

[manhattes]

Of course there is a way to convert it - that's what STR_TO_DATE() is doing.

 

BTW, date column name will need the backticks - I just noticed you haven't got rid of the spaces as advised.

Thank you I tried that. I think it is because the format thinks month and day are separate.

The format is Day of the Week, Month Day, Year

Seems like this is getting more complicated...

How do I make it separate the month and day without a comma?

[manhattes]

I tried this too. No Luck

SELECT * FROM CleanedCalendar 
WHERE STR_TO_DATE(`Completion Date`, '%W %M %D %Y') 
   BETWEEN CURDATE() AND CURDATE() + INTERVAL 30 DAY;

[mac_gyver]

Is there a way to convert it and save it to a new column that is DATE and not VARCHAR?

 

 

add the new column to your table and run one UPDATE query that populates the new column from the existing column's values.

 

 

How do I make it separate the month and day without a comma?

 

 

if your existing data contains the commas and spaces, the format-string you use as the parameter in the STR_TO_DATE() mysql function must contain those same characters.

 

 

Seems like this is getting more complicated...

 

 

that's why a correct design is important, so that you don't have to keep going back and fixing things.

[manhattes]

add the new column to your table and run one UPDATE query that populates the new column from the existing column's values.

 

 

if your existing data contains the commas and spaces, the format-string you use as the parameter in the STR_TO_DATE() mysql function must contain those same characters.

 

 

 

that's why a correct design is important, so that you don't have to keep going back and fixing things.

Thanks I got it to work from Barands direction, I had to change the format mask. My script works like a charm. thanks guys.