Skip to main content
G
gigasaurus
Inspiring
July 31, 2009
Question

Writing / Reading Date and time to DateTime field in MySQL

  • July 31, 2009
  • 2 replies
  • 1656 views

I have never had a good way to the date/time to a datetime field in MySQL.

I'd prefer not to use the built-in timestamping in MySQL (because I want to have more control based on the situation)

I don't get any errors when I usedateformat:    #dateformat(Now(), "yyyy-mm-dd hh:mm:ss")#

But the "minutes" (:mm:) always get the "month" value (-mm-)

Could someone please give me a good definitive way to do this?

I appreciate it.

This topic has been closed for replies.

2 replies

M
mack_
Participating Frequently
August 2, 2009

You should use cfqueryparam and pass the "raw" date, not a formatted

date (use cf_sql_date for a date field and cf_sql_time for a time

field):


]]>

There's no need to format the date before passing it to cfqueryparam

(the tag recognized automatically that it's a date).

Mack

D
Dan_Bracuk
Inspiring
July 31, 2009

createdatetime()

G
gigasaurusAuthor
Inspiring
July 31, 2009

Thanks for responding.

Its the formatting of the data as I insert it into MySQL where I have my problem.

The database has the field set to DATETIME.  So I want to format correctly, but I want to make sure my MONTH and MINUTES don't end up both having the MONTH value.

<CFSET MyVariable = createdatetime()>

<cfquery datasource="MyDatabase">
    insert into mm_persona_session
        (
        persona_datetime
        )
    values
        (
        '#dateformat(MyVariable, "yyyy-mm-dd hh:mm:ss")#'
        )
</cfquery>

so how do I correct the dateformat line above?

Dileep_NR
Dileep_NR
Inspiring
August 1, 2009

hi

please try this

<cfset newDate = dateformat(now(), "yyyy-mm-dd") &  timeformat(now(), " hh:mm:ss")>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices