where datediff

[KeithLaw999]

Gday i have a query which i want to select all where date is 28 days old

but i cant get it working, can someone tell me what is wrong with my code?

[Stressed_Simon]

Remember that cfquery is putting a string together to send to the database so DATEDIFF(d,InDate,Now()) is wrong it should be DATEDIFF(d,InDate,#Now()#). However, depending on what db you are using I would recommend that you use the internal time on the database so on SQL Server you would use DATEDIFF(d,InDate,getDate())

[KeithLaw999]

hi yes i tried that already but i get this error

Syntax error or access violation: 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 'getDate()) >= 28' at line 3

any ideas?

[Stressed_Simon · Accepted solution]

Well it turns out in MySQL that it is Now() to get the current date, so what you wrote originally would have worked if you had used the correct syntax for dateDiff().

Check this for all the date time functions:-
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Here what is what you need:-
DATEDIFF(Now(),InDate) >= 28