Date, between date column and date column -3 days

[lasha]

Hellow, i need help please, writing code and it doesn't work. please help...

 

Here it is

WHERE start_date BETWEEN 'start_date".strtotime('-3 day')."' AND 'start_date'";

without this code everithing works fine

 

Thank you

[Jacques1]

I have no idea what that is supposed to mean.

 

But there's no reason for this PHPMySQL mixture, anyway. MySQL is perfectly capable of doing date calculations.

[lasha]

Thanks for post Jacques1,

 

I want to show records 3days erlier before it starts and if started it must dissapear

[lasha]

I made research but I can't reach the goal... Don't know what am i doing wrong... can you help with this?

 

i don't know how to write correctly 

WHERE start_date BETWEEN start_date -3 day AND start_date

Thanks

[Jacques1]

The blue underlined text in my reply is a link. You need to click it and read the text.

 

I'm not sure where you've searched, but the very first function in the link above is already the one you need.

[lasha]

No no im going crazy... i cant figure out how to write correctly. I tryed every combination and it doesnt show what i need... why it is so hard  to do...?

"SELECT metal, plastic, all_matts, instrmnts FROM take WHERE start_date BETWEEN ADDDATE('start_date', INTERVAL 3 DAY) AND start_date";

im new to all this and dont know how to write it correctly.. plz help

[Barand]

You are comparing the start date with itself so you will always get a match.

 

Explain precisely what you are trying to achieve, preferably with example data and what output you expect.

[lasha]

Thank you for post

 

Im trying to get records 3 days earlier before start date... and when start date will be today it must dissapear. Thats all story... 

 

Output must be names with start date:

Rocky Star Plastic - 08/05/2014

 

I have no idea how to write it correctly

[Barand]

So if today is the 5th you want to show records with a start_date between 6th and 8th ?

 

If so

... WHERE start_date BETWEEN CURDATE() + INTERVAL 1 DAY AND CURDATE() + INTERVAL 3 DAY

[lasha]

Yes it is close to it but i explain better,

 

if start date is 8 and today is 5th, i want to show this record between 5 and 8.

So i thought if it there will be between start_date (8th) minus 3 day and start_date.

 

Is this good logical solution?

[cyberRobot]

if start date is 8 and today is 5th, i want to show this record between 5 and 8.

So i thought if it there will be between start_date (8th) minus 3 day and start_date.

 

These two statement don't quite line up.

 

With today being Aug. 5, do you want to get all entries from Aug. 2 to Aug. 5? Or do you want the entries from Aug. 5 to Aug. 8?

[cyberRobot]

With today being Aug. 5, you can get the entries from Aug. 5 to Aug. 8 with the following WHERE clause:

... WHERE start_date BETWEEN 
    CURDATE() 
    AND 
    CURDATE() + INTERVAL 3 DAY

 

To get the entries from Aug. 2 to Aug. 5, you can use this instead:

... WHERE start_date BETWEEN 
    CURDATE() - INTERVAL 3 DAY 
    AND 
    CURDATE()

 

[lasha]

cyberRobot thank you for attention

 

There is db records:

 

name  |  start_date    | end_date

rocky   |  08/09/2014    | 08/12/2014

nancy  |  08/13/2014    | 08/15/2014

arry     |  08/16/2014    | 08/19/2014

 

I want to show records 3 day before start_date ... this is the goal...  t must count down 3 days, 2 days ,1 days. 

thanks

[Barand]

 

 

08/09/2014

 

Is that the date format that you are storing in your database?

 

If so, date arithmetic, functions and comparisons will not work. You need to convert them to yyyy-mm-dd format, column type DATE, or convert them in every record with STR_TO_DATE() when ever you run your query - which isn't very efficient.

[lasha]

Barand yes it is the date fromat.

 

damn what to do? if i use STR_TO_DATE() what will heppen?

[CroNiX]

What is the column type for your date field?

[cyberRobot]

Barand yes it is the date fromat.

 

if i use STR_TO_DATE() what will heppen?

 

More information about STR_TO_DATE() can be found here:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

 

 

 

damn what to do?

 

You could build a quick script to convert all the dates. Of course, you would need to modify any existing scripts which use those date columns.

 

If that's an issue, you could save the newly formatted dates into new columns. You would be double entering the dates, but this would give you time to modify the your scripts so they use the new columns. Once all of your scripts are using the new date columns, the old ones can be removed.

[lasha]

CroNiX its varchar

 

cyberRobot but there is 5000+ record in db

 

Is there any other way to do that... i don't care in this step if it will be slow search time.

 

I have no time for modifying this huge script for now... I have a panic  

[cyberRobot]

As Barand suggested, you could use STR_TO_DATE() to convert the date on the fly.

[Barand]

try

WHERE STR_TO_DATE(start_date, '%m/%d/%Y') BETWEEN 
    CURDATE() 
    AND 
    CURDATE() + INTERVAL 3 DAY

[lasha]

Ok i used STR_TO_DATE()... But i wanted to know how to write correctly the qery WHERE piece.

 

name  |  start_date    | end_date

rocky   |  08/09/2014    | 08/12/2014

nancy  |  08/13/2014    | 08/15/2014

arry     |  08/16/2014    | 08/19/2014

 

This is where i came now...  

WHERE STR_TO_DATE(start_date, '%m-%d-%Y') BETWEEN STR_TO_DATE(start_date, '%m-%d-%Y') - INTERVAL 3 DAY AND STR_TO_DATE(start_date, '%m-%d-%Y')

but it doesn't work

[lasha]

Barand OMG it seems to work!!!

 

Perfect Answer!   :D

 

 

try

WHERE STR_TO_DATE(start_date, '%m/%d/%Y') BETWEEN 
    CURDATE() 
    AND 
    CURDATE() + INTERVAL 3 DAY

I appreciate your help <3 

Thank you ALL for helping....

 

Barand i'll mark your answer as solution 

[CroNiX]

You're building on an unstable foundation and trying to find workarounds. Sooner or later, your unstable building can fall. Especially if you end up getting a lot of traffic.

 

The proper way to do this, and it really wouldn't take long, is to add a new column to the db for your date. Then select all your existing "text" dates, convert them with STR_TO_DATE(start_date, '%m/%d/%Y') and store them in the new column.

 

It REALLY wouldn't take long to FIX the problem instead of masking it. It can be done entirely in SQL. You're also making mysql having to work a bit harder to convert your text dates to real dates.

 

http://stackoverflow.com/questions/16444501/convert-text-date-to-datetime

[lasha]

CroNiX thank you for the support and attention. This is not a mask for what I did, I did it because of lack of time. Inevitably, in the near future, I'll do it as you suggested. When I finish, I'll come back and write about how I did and what I got. 

 

 

Thank you again