Jump to content

Date, between date column and date column -3 days


lasha
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 :(

Edited by lasha
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Edited by cyberRobot
Link to comment
Share on other sites

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()

 

Link to comment
Share on other sites

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 :)

Link to comment
Share on other sites

 

 

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.

Link to comment
Share on other sites

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.

Edited by cyberRobot
Link to comment
Share on other sites

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 :D  :suicide:

Edited by lasha
Link to comment
Share on other sites

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 :(

Link to comment
Share on other sites

Barand OMG it seems to work!!!

 

Perfect Answer!  :D :D :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  ::)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Edited by lasha
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.