bkpaul Posted November 13, 2007 Share Posted November 13, 2007 Hi Folks, I have this MySQL statement that doesn't work could someone point me in the right direction please SELECT r.`matchdate`, r.`hometeam`, r.`awayteam`, r.`resultstatus` FROM results r WHERE r.`matchdate` < #newDate# AND r.`resultstatus`="Enter Result" ORDER BY r.`hometeam`, r.`awayteam`; where an example #newdate# value would be {ts '2007-08-30 00:00:00'} Basically I want to list all records in 'matchdate' before a given date 'matchdate' is in the format 30/08/2007 but I converted that to a timestamp using the folowing to give the variable #newdate# <cfset arr = listToArray("2007/08/30", "/")> <cfset newDate = createDate(arr[1], arr[2], arr[3])> Thanks for any help you can offer Link to comment https://forums.phpfreaks.com/topic/77191-problem-with-dates-need-help-please/ Share on other sites More sharing options...
Barand Posted November 13, 2007 Share Posted November 13, 2007 You can't compare dates directly in that format (ie 30/08/2007). It just won't work, it will put 25/12/2007 before 30/08/2007 You'll need to convert with STR_TO_DATE to put it into correct datetime format ... WHERE STR_TO_DATE(r.matchdate, '%d/%m/%Y') < '$newdate' Better to store dates in correct format (yyyy-mm-dd) to begin with. Link to comment https://forums.phpfreaks.com/topic/77191-problem-with-dates-need-help-please/#findComment-390854 Share on other sites More sharing options...
bkpaul Posted November 13, 2007 Author Share Posted November 13, 2007 Cheers for the reply, still having problems I have statement 1 SELECT date AS targetdate,datesindex FROM dates ORDER BY datesindex This produces the expected result 30/08/2007 1 06/09/2007 2 13/09/2007 3 20/09/2007 4 27/09/2007 5 04/10/2007 6 11/10/2007 7 18/10/2007 8 25/10/2007 9 01/11/2007 10 08/11/2007 11 15/11/2007 12 22/11/2007 13 29/11/2007 14 06/12/2007 15 13/12/2007 16 20/12/2007 17 03/01/2008 18 10/01/2008 19 17/01/2008 20 24/01/2008 21 31/01/2008 22 07/02/2008 23 14/02/2008 24 21/02/2008 25 28/02/2008 26 06/03/2008 27 13/03/2008 28 20/03/2008 29 27/03/2008 30 03/04/2008 31 10/04/2008 32 17/04/2008 33 24/04/2008 34 Then I use a cfselect statement to choose a date from the list above (ignore datesindex for the moment I may or maynot use that functionality at some point) this cfselect statement chooses a week from above and I want to return all records before that date a sample return from the cfselect statement is 25/10/2007 and is stored in form variable called #form.getweek# then I use your suggestion SELECT r.`matchdate`, r.`hometeam`, r.`awayteam`, r.`resultstatus` FROM results r WHERE STR_TO_DATE(r.matchdate, '%d/%m/%Y') < '#form.getweek#' ORDER BY r.`hometeam`, r.`awayteam`; I did change the variable to #form.getweek# due to another reason MySQL query browser returns the result of the query as: SELECT r.`matchdate`, r.`hometeam`, r.`awayteam`, r.`resultstatus` FROM results r WHERE STR_TO_DATE(r.matchdate, '%d/%m/%Y') < '29/11/2007' ORDER BY r.`hometeam`, r.`awayteam`; datasource cdsl record count 0 execution time 0ms I was using but dropped this conversion statement (which I got else where and don't understand) <cfset arr = listToArray("#form.getweek#", "/")> <cfset newDate = createDate(arr[1], arr[2], arr[3])> But it returned an error " DAY_OF_MONTH" which threw me So no records returned.... can you help again please its pulling out hair time Link to comment https://forums.phpfreaks.com/topic/77191-problem-with-dates-need-help-please/#findComment-391007 Share on other sites More sharing options...
Barand Posted November 13, 2007 Share Posted November 13, 2007 I've already told you dates in the form dd/mm/yyyy are as much use as a chocolate teapot when it comes to date comparisons If you are using MySql, lose the #...# from the date string. WHERE STR_TO_DATE(r.matchdate, '%d/%m/%Y') < '2007-11-29' Link to comment https://forums.phpfreaks.com/topic/77191-problem-with-dates-need-help-please/#findComment-391018 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.