SQL Date Calculations

I have a table with with Last_Modified field as VARCHAR(55) which stores dates in the format of 2/15/08 1:34 PM. What I need to do is query the database and select ALL rows that have the Last_Modified field >= 6 days from the current date/time. It appears I will need to convert the dates so I can use the mysql calculations but I'm unsure where to begin on this.

You first have to rework your date from the VARCHAR field into a 'normal' MySQL date format like

Expand|Select|Wrap|Line Numbers

1. .... STR_TO_DATE(field_name, '%m/%d/%y') ....;

and then use that result to compare with the MySQL CURRDATE() values, like

Expand|Select|Wrap|Line Numbers

1. SELECT * from table_name WHERE DATEDIFF(STR_TO_DATE(field_name, '%m/%d/%'), CURRDATE()) >= 6;

Ronald

Yes STR_TO_DATE also can handle the time part. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

Better look at the MySQL documentation on STR_TO_DATE or the Date and Time function chapter in same manual HERE

Ronald

In your start post you said that your data format was varchar mm/dd/yy. So that is the string that you must convert to a normal MySQL date with

Expand|Select|Wrap|Line Numbers

1. STR_TO_DATE(field_name, '%m/%d/%y') ....;

So why are you now using

Expand|Select|Wrap|Line Numbers

1. str_to_date(field_name, '%Y-%c-%e')

Ronald

I made a mistake it should be

Expand|Select|Wrap|Line Numbers

1. STR_TO_DATE(field_name, '%m/%d/%y')

however it doesnt seem to be returning anything. When I just run DATEDIFF inside of phpMyAdmin SQL it gives me a syntax error

Expand|Select|Wrap|Line Numbers

1. Error
2.  
3.               DATEDIFF( STR_TO_DATE(  '2/15/08',  '%m/%d/%y'  ) , CURDATE(  )  )      
4.  
5.   #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATEDIFF(STR_TO_DATE('2/15/08','%m/%d/%y'), CURDATE())' at line 1

Ok I got it to work the CURDATE should of been the first param. So now I have

Expand|Select|Wrap|Line Numbers

1. $sql = "SELECT * FROM opencases_daily INNER JOIN tsms WHERE ".
2.                 "DATEDIFF(CURDATE(),STR_TO_DATE(Last_Out_Note, '%m/%d/%y')) >= 6 ".
3.                 "AND Assigned_To=tsms.Name AND tsms.uid='".$_REQUEST[1].
4.                 "' ORDER BY CAST(Last_Out_Note AS UNSIGNED) DESC";

Thanks for your help!

is there a way to order by the datediff?

simplified version

Expand|Select|Wrap|Line Numbers

1. SELECT *, DATEDIFF(CURDATE(),STR_TO_DATE(data, '%m/%d/%y')) as diff FROM a where DATEDIFF(CURDATE(),STR_TO_DATE(data, '%m/%d/%y')) >= 6 order by diff;

Ronald