Problem with dates need help please

[bkpaul]

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

[Barand]

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.

[bkpaul]

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

 

[Barand]

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'