Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I insert a timestamp into a SQL database using coldfusion?

Posted on 2009-07-07
8
Medium Priority
?
750 Views
Last Modified: 2013-12-20
I'm created a database driven site in coldfusion and have run up against a wall, I haven't been able to find a clear answer on the web. I'm trying to store the date and time a record is created or updated in a database. I've been unsuccessful in storing and inserting the value from now() into the database. I get error messages that the insert statement doesn't understand ts { .... }. The field data type in the database is date/time. Please help!
0
Comment
Question by:jsoenke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24800335
try getDate()
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24800337
and also what is the datatype  you using int your DB
0
 

Author Comment

by:jsoenke
ID: 24800396
Here's what happens:

Variable GETDATE is undefined.

See the code below...

Thanks
	INSERT INTO facilities
	(facility_name,facility_addr,facility_city,facility_state,facility_country,facility_postal,facility_market,facility_type,facility_created)
	VALUES ('#requiredFacilityName#','#requiredFacilityAddress#','#city#','#state#','#country#','#requiredFacilityPostal#','#market#','#getDate()#')

Open in new window

0
Plesk WordPress Toolkit

Plesk's WordPress Toolkit allows server administrators, resellers and customers to manage their WordPress instances, enabling a variety of development workflows for WordPress admins of all skill levels, from beginners to pros.

See why 2/3 of Plesk servers use it.

 
LVL 19

Accepted Solution

by:
erikTsomik earned 2000 total points
ID: 24800403
change it to this
  INSERT INTO facilities
        (facility_name,facility_addr,facility_city,facility_state,facility_country,facility_postal,facility_market,facility_type,facility_created)
        VALUES ('#requiredFacilityName#','#requiredFacilityAddress#','#city#','#state#','#country#','#requiredFacilityPostal#','#market#',getDate())
0
 

Author Comment

by:jsoenke
ID: 24800439
Now I get the following error:

FUNCTION elevated.getDate does not exist

I found that GetDate() is for MS-SQL, Now() is for MySQL which is what I'm using. However, when I removed the #'s and used Now(), it worked!

Thank you for the help!
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 24800443
getdate() is not a cf function - it is MS SQL built-in function. thus do not surround it with ##.

PS: Use <cfqueryparam> when inserting user-defined values into your db!
PPS: you may also want to properly scope your variables, i.e. #FORM.requiredFacilityName#

Azadi
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24800452
Also the best practice is to use <cfqueryparam for future references
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24805760
> VALUES (...... '#getDate()#'   )
> Now() is for MySQL which is what I'm using. However, when I removed the #'s
> and used Now(), it worked!

It probably would have worked if you had removed the single quotes.  

As you have discovered both CF and MySQL have a now() function.   To use the CF function, use ## signs, but _no_ single quotes (because the function returns a date/time object, not a string).

INSERT ....
VALUES (   #now()# )

To use the MySQL version of that function, do _not_ use ## signs or single quotes:

INSERT ....
VALUES (   now() )
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . It goes without saying that technology has transformed society and the very nature of how we live, work, and communicate in ways that would’ve been incomprehensible 5 ye…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question