Jump to content

Displaying Future Dates as Result


manhattes
Go to solution Solved by Barand,

Recommended Posts

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 by manhattes
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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;
Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.