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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

azadisaryevCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.