http://coldfusion.com logo
Join Slack
Powered by
How can I write the date to MySQL with ColdFusion?...
# cfml-beginners
j
How can I write the date to MySQL with ColdFusion? I tried 
#CreateODBCDate(Now)#
However I get an error, so I know that this is wrong. Any help would be greatly appreciated
p
CreateODBCDate(Now())
a
Did you mean to type 
Now()
(note the parenthesis)? That should work.
j
@Patrick thanks so much for your help, I changed the statement to 
"#CreateODBCDate(Now())#"
and it works! I have to be careful with my parenthesis.
a
Um... CFML doesn't use ODBC any more (and hasn't for over 20 years). So not sure that's part of the answer to this question unless asking the question for CF5 or earlier. You should be able to pass a CFML DateTime object directly to the DB, without monkeying with it. It's difficult to say what your problem is given all you say is "However I get an error", but neglect to tell us what the error is. I find the error message usually explains what's going wrong...
@johnbarrett was your error "The value class coldfusion.runtime.CFPageMethod cannot be converted to a date."?
j
@Adam Cameron the code is working fine now and writes the data to the database. What's the best practice for writing dates into a MySQL database with ColdFusion? You say above that I should use DateTime instead, do you have a code example?
p
Just now() is good enough
👍 2
a
When you said "date", I thought you meant in a literal sense: current date only, without the time portion. Despite the antiquated name, that is a valid use for 
CreateODBCDate()
IMO, (unless Adobe has replaced it with another function that returns a date only). For queries you could also use cfqueryparam and cfsqltype=date to trim the time. Though if you want both date and time, yeah 
now()
is the shortest route
a
DateTime instead, do you have a code example?
Any expression that returns a DateTime object. eg, as someone mentioned: 
now
returns a DateTime. So does 
createDate
. So do most date functions (other than the ones that convert a date to something else, obvs.) The functions with "ODBC" in their names are designed for converting DateTime objects to a format ODBC understands. Which is seldom a requirement these days. (Although I just checked, and they don't seem to actually do what they claim these days anyhow: 
createOdbcDateTime
just returns a DateTime object (
coldfusion.runtime.OleDateTime
), and 
createOdbcDate
returns a 
coldfusion.runtime.OleDate
)
j
This is how I am using the cfqueryparam, and I changed the date field to joining_date. Is this the best way to use cfqueryparam on all the fields?
Copy code
<cfquery result="qResult">                INSERT INTO users
                (
                    first_name,
                    last_name,
                    email,
                    joining_date
                )
                VALUES
                (
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.first_name#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.last_name#">
, <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.member_email#">
, <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Now()#">
                )
</cfquery>
a
Yep. I'd probably use 
queryExecute
rather than 
<cfquery>
to get rid of some of the code-bloat, but that is the correct approach, yes. I would not use hungarian notation (-ish) on my variable names though (eg: 
qResult
)
👍 1
That said, the whole variable name there is questionable: 
result
doesn't impart much information to the person reading the code. Also... an 
INSERT
query doesn't ever return anything in there anyway, does it? What are you using it for?
Open in Slack
PreviousNext
Powered by