manhattes Posted July 31, 2015 Share Posted July 31, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 Or maybe something like this? SELECT CleanedCalendar WHERE `Completion Date`( NOW( ) + INTERVAL 1 MONTH ) (Doesnt work Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 31, 2015 Share Posted July 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 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 Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 31, 2015 Share Posted July 31, 2015 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; Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 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? Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted July 31, 2015 Solution Share Posted July 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 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? Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 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; Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted July 31, 2015 Share Posted July 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
manhattes Posted July 31, 2015 Author Share Posted July 31, 2015 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.