Datetime Field in MySQL 5.0 with Coldfusion

RickEpnet
RickEpnet used Ask the Experts™
on
I pull a date / time from the web and populate it to a MySQL table but I am getting the wrong time. I am sure this is something easy but I cannot figure it out.

For Example
Thu, 28 Jan 2010 08:23:00 PST
Will come in at 8PM I assume because of the P in PST. The reason I amuse this because this time populates correctly. Thu, 28 Jan 2010 17:25:43 GMT

Coldfusion timeformat does the same thing.

I am not using timeformat function when I populate the database.

I thought of using the mid() or right() function to strip out the PST but I was hoping for a better idea than that.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HainKurtSr. System Analyst

Commented:
whats the code for "I pull a date / time from the web and populate it to a MySQL" and also for "I am getting the wrong time"

Author

Commented:
This is a Time Zone code. But if you want to ask a Question you should post your own question.
use cfqueryparam with the cf_sql_type of datetime and it should format your date specifically for your data source

<cfqueryparam cf_sql_type ="datetime" value="#myDate#">

btw...if you are -not- using cfqueryparam you are at extreme risk of sql injection if you have a publicly facing site.







11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

oops..sorry there is not datetime it's just date

<cfqueryparam cf_sql_type ="date" value="#myDate#"> but it will -handle- datetime values

Author

Commented:
I use this <cfqueryparam value="#form.storydate#" cfsqltype="CF_SQL_DATE"> I get this

2010-01-28 00:00:00
Most Valuable Expert 2015
Commented:
I'm a little confused about the issue here...

> <cfqueryparam value="#form.storydate#" cfsqltype="CF_SQL_DATE">

    That will truncate any "time" and just return the date ie 01/28/2010 only. If you need the date and time, use
    cf_sql_timestamp.

    If that's not what you're asking could you please post and example showing
    1) The code used (and any the value of any variables)
    2) The actual results versus the desired results

Author

Commented:
SidFishes pointed in the right direction but agx gave a complete answer. Thanks!!

Most Valuable Expert 2015

Commented:
> <cfqueryparam cf_sql_type ="datetime" value="#myDate#">
> <cfqueryparam cf_sql_type ="date" value="#myDate#">

For PAQ purposes, I think those are just typos

ie  cf_sql_timestamp (instead of datetime)  and
     cf_sql_date

Note: The values need to be prefaced with "cf_sql_"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial