SQL Date Calculations | Member | | Join Date: Mar 2008
Posts: 90
| | |
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.
|  | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: SQL Date Calculations
Quote:
Originally Posted by arggg 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 - .... STR_TO_DATE(field_name, '%m/%d/%y') ....;
and then use that result to compare with the MySQL CURRDATE() values, like - SELECT * from table_name WHERE DATEDIFF(STR_TO_DATE(field_name, '%m/%d/%'), CURRDATE()) >= 6;
Ronald
| | Member | | Join Date: Mar 2008
Posts: 90
| | | re: SQL Date Calculations
Quote:
Originally Posted by ronverdonk You first have to rework your date from the VARCHAR field into a 'normal' MySQL date format like - .... STR_TO_DATE(field_name, '%m/%d/%y') ....;
and then use that result to compare with the MySQL CURRDATE() values, like - SELECT * from table_name WHERE DATEDIFF(STR_TO_DATE(field_name, '%m/%d/%'), CURRDATE()) >= 6;
Ronald Is there a way to add the time in there as well so for example once that exact hour/minute hit that can be counted as 6 days? Also if this is not possible will str_to_date take out the time for me?
| | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: SQL Date Calculations
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
| | Member | | Join Date: Mar 2008
Posts: 90
| | | re: SQL Date Calculations
Quote:
Originally Posted by ronverdonk 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 I can't get the query to return nothing when infact there is data that should match this. - SELECT * FROM opencases_daily INNER JOIN tsms WHERE DATEDIFF(STR_TO_DATE(Last_Out_Note, '%Y-%c-%e'), CURDATE()) >= 6 AND Assigned_To=tsms.Name AND tsms.uid='12' ORDER BY CAST(Last_Out_Note AS UNSIGNED) DESC
| | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: SQL Date Calculations
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 - STR_TO_DATE(field_name, '%m/%d/%y') ....;
So why are you now using - str_to_date(field_name, '%Y-%c-%e')
Ronald
| | Member | | Join Date: Mar 2008
Posts: 90
| | | re: SQL Date Calculations
Quote:
Originally Posted by ronverdonk 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 - STR_TO_DATE(field_name, '%m/%d/%y') ....;
So why are you now using - str_to_date(field_name, '%Y-%c-%e')
Ronald I made a mistake it should be - 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 - Error
-
-
DATEDIFF( STR_TO_DATE( '2/15/08', '%m/%d/%y' ) , CURDATE( ) )
-
-
#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
| | Member | | Join Date: Mar 2008
Posts: 90
| | | re: SQL Date Calculations
Quote:
Originally Posted by arggg I made a mistake it should be
- 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 - Error
-
-
DATEDIFF( STR_TO_DATE( '2/15/08', '%m/%d/%y' ) , CURDATE( ) )
-
-
#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 -
$sql = "SELECT * FROM opencases_daily INNER JOIN tsms WHERE ".
-
"DATEDIFF(CURDATE(),STR_TO_DATE(Last_Out_Note, '%m/%d/%y')) >= 6 ".
-
"AND Assigned_To=tsms.Name AND tsms.uid='".$_REQUEST[1].
-
"' ORDER BY CAST(Last_Out_Note AS UNSIGNED) DESC";
Thanks for your help!
| | Member | | Join Date: Mar 2008
Posts: 90
| | | re: SQL Date Calculations
Quote:
Originally Posted by arggg Ok I got it to work the CURDATE should of been the first param. So now I have
-
$sql = "SELECT * FROM opencases_daily INNER JOIN tsms WHERE ".
-
"DATEDIFF(CURDATE(),STR_TO_DATE(Last_Out_Note, '%m/%d/%y')) >= 6 ".
-
"AND Assigned_To=tsms.Name AND tsms.uid='".$_REQUEST[1].
-
"' ORDER BY CAST(Last_Out_Note AS UNSIGNED) DESC";
Thanks for your help! is there a way to order by the datediff?
| | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: SQL Date Calculations
Quote:
Originally Posted by arggg is there a way to order by the datediff? simplified version - 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
| | Member | | Join Date: Mar 2008
Posts: 90
| | | re: SQL Date Calculations
Quote:
Originally Posted by ronverdonk simplified version - 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 ahhh thanks thats what I had i just was putting it in the wrong place!
| | Moderator | | Join Date: Jul 2006 Location: The Netherlands
Posts: 4,139
| | | re: SQL Date Calculations
Ok, all solved? Then see you around some time.
ROnald
|  |
Similar MySQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,272 network members.
|
