Delete query using datediff

Hello experts.
I'm trying to set a query in my login page thats is automatic deleting user registration that have not activate there account.
here is my query but unfortunately it is not working.
<cfset variables.SqlTimenow ="#DateFormat(now(),"dd/mm/yyyy") & '  ' & timeformat(now(),'h:mm:ss tt')#">
<!--- delete unactvated accounts --->
<cfquery datasource="#request.dsn#">
DELETE
FROM user
WHERE activation = <cfqueryparam value="0" cfsqltype="cf_sql_numeric">
AND DateDiff("h",dateregister,SqlTimenow) > <cfqueryparam value="24" cfsqltype="cf_sql_numeric">
</cfquery>
I get the error:
Incorrect syntax near the keyword 'user'.
 
(dateregister is my column with the registration date in form:dd/mm/yyyy h:mm:ss tt
Any idea how to do this?
LVL 2
PanosAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
azadisaryevConnect With a Mentor Commented:
what is your db, and what is the datatype of your dateregister column (hope it is a date/timestamp type, not varchar...)?

if dateregister column is a date/timestamp type column, then you should:
- either use your db's datediff function equivalent (and then use it without # around it, as it is a db function, not a cf function).
for example, in MySQL you could do:
AND HOUR(TIMEDIFF(NOW(), dateregister)) > 24
[MySQL has a DATEDIFF() function, but it returns difference in days, not hours, that's why one needs to use HOUR() in combination with TIMEDIFF() functions]

- or do a direct date comparison instead:
AND dateregister < <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateadd('h', -24, now())#">
or use db-specific functions, for example in MySQL you could do:
AND dateregister < DATE_SUB(NOW(), INTERVAL 24 HOUR)


if dateregister column is a text type column, then you better convert it to a date type right now before it's too late...

Azadi
0
 
PanosAuthor Commented:
azadi it is working but i have in my db two other columns as varchar that have the lastlogin time and a cookie time.I was following the instructions here:http://www.experts-exchange.com/Software/Server_Software/Web_Servers/ColdFusion/Q_24136992.html and now i have to change this too.


0
 
PanosAuthor Commented:
Thank you very much
regards
panos
0
All Courses

From novice to tech pro — start learning today.