DateTime using Flex and CF? Head scratcher! :p

[loki421]

Hi all,

I have a column in my MySQL database typed as DATETIME, and i am trying to pass the value from a datefield in Flex via ColdFusion to the db, but for some reason it's just not hapening, i keep getting errors, here's one of them:


faultCode:Server.Processing faultString:'Unable to invoke CFC - Error Executing Database Query.' faultDetail:'Data truncation: Incorrect datetime value: '30/06/2009' for column 'date' at row 1'


So here's the code i'm using to send it to the db (from Flex):

private function insertHandler(event:MouseEvent):void
{
ro.Insert(dateField.text);
}


('ro' is my remote object and 'Insert' is my method for the CFC)

so here's my CFC code:

<cffunction name="Insert" access="remote" returntype="Any">

<cfargument name="dateInput" type="Date" required="yes">

<cfquery name="qInsert" datasource="#data#">
INSERT INTO testing (date)
VALUES ('#arguments.dateInput#')
</cfquery>



</cffunction>


I've tried all sorts of different syntax to get this to work, and i'm just out of ideas, do i have to cfset the argument to a data type dd/mm/yyyy (as i belive the MySQL column is in?) or do i have to send another value from the dateField in Flex? ie data instead of text? I have tried that but it didn't seem to work.

Really hoping someone here can help me sort this out :D

Many thanks in advance guys and gals!!!!!

[Inigoesdr]

do i have to cfset the argument to a data type dd/mm/yyyy (as i belive the MySQL column is in?) or do i have to send another value from the dateField in Flex? ie data instead of text? I have tried that but it didn't seem to work.
It looks like you've already come to this conclusion, but the problem seems to be with the database query function itself. I would suggest logging the generated query so you can run it manually if need be for testing, and inspect it for errors. The MySQL DATETIME format is 'YYYY-MM-DD HH:MM:SS', so you need to update that. If you just want to store the date you can change the column to a DATE field instead, though MySQL will store a date with a time of midnight if you just specify the date for DATETIME. Passing it in as a string will work fine; you don't need to convert it to "data".

[loki421]

Thanks, but i've got my MySQL column set to DATE and not DATETIME, this is why i'm getting confused here, it's displaying in the query browser as 2009-06-20 with no time, and yet when it comes through the app via CF it's showing time too??

I've heard that you can format the date via the CF query (DateFormat etc) but i've done some Googling and not found anything that really makes sense.... Any ideas what's going on here? Is there a way to format MySQL directly?

[Inigoesdr]

Is there a way to format MySQL directly?
There is, in fact, a DATE_FORMAT() (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format) function for MySQL. MySQL won't be adding the time to your column if it is DATE to begin with though; something in your app might be doing that.

[loki421]

You're exactly right! I spent 8 hours last night trying to figure this one out, and finally found a way around it. It turned out Flex was adding the time (or CF), and i used this code to rectify the problem, so if anyone else is having trouble... here's the answer :)


<mx:Script>
<![CDATA[
private var myDate:Date;

private function init():void
{
myDate = data.Date; // this is the date from the database, i'm using this in a list control with an item renderer to this component
myDateLablel.text=DateDisplay.format(myDate);
{

]]>
</mx:Script>

<mx:DateFormatter id="DateDisplay" formatString="DD MMMM YYYY"/>

<mx:Label x="10" y="10" text="{myDate}" id="myDateLabel" />


Hope this helps, and thanks for the replies :D