Date Insert 2010-08-02T00:00:00 from CFGRID to Access DB Insert

[Al_D_]

I have a datagrid that is bound to a cfc.  The datagrid has a date in the type = date column and the grid is bound to an update query to an Access database in the cfc.  The date that is being passed from the grid is this format 2010-08-02T00:00:00.  It is throwing an error on the update for the sql update.  How do I convert/format the date to get it into the database.

ERROR:  2010-08-02T00:00:00 is an Invalid date or time string

Code for Grid:

<cfform>
        <cfgrid
            name="basicGrid"
            bind="cfc:bindFeeder.getData({cfgridpage}, {cfgridpagesize}, {cfgridsortcolumn}, {cfgridsortdirection})"
            format="html"
            autowidth="true"
            striperows="true"
            title="Bookings Tracker Data Entry / Update Grid"
            collapsible="true"
            pagesize="20"
            selectmode="edit"
            onchange="cfc:bindFeeder.editData({cfgridaction}, {cfgridrow}, {cfgridchanged})"
            insert="true"
            delete="true"
            insertbutton="Create Booking"
            deletebutton="Delete Booking"
            onerror="catchGridError"
        >
       
            <cfgridcolumn name="ISU" header="ISU" values="#ISUData#" />
            <cfgridcolumn name="Market" header="Market" values="#MarketData#" />
            <cfgridcolumn name="ClientMgr" header="Client Mgr" select="false" />
            <cfgridcolumn name="PrjStartDate" header="Prj Start" type="date" />
            <cfgridcolumn name="CompanyName" header="Company Name" />
        </cfgrid>
    </cfform>

CFC Code:

<cffunction name="editData" access="remote" returntype="boolean">
        <cfargument name="gridaction">
        <cfargument name="gridrow">
        <cfargument name="gridchanged">

        <cfswitch expression="#Arguments.GridAction#">
       
        <!--- UPDATE Data IN the database --->
            <cfcase value="U">
               
                    <cfquery datasource="#REQUEST.dsn#">
                    UPDATE BookingsInfo
                    SET
                        ILT = ILT
                        <cfif StructKeyExists(Arguments.GridChanged, "ISU")>
                            , ISU = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridChanged.ISU#" />
                        </cfif>
                        <cfif StructKeyExists(Arguments.GridChanged, "Market")>
                            , Market = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridChanged.Market#" />
                        </cfif>
                        <cfif StructKeyExists(Arguments.GridChanged, "CompanyName")>
                            , CompanyName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridChanged.CompanyName#" />
                        </cfif>
                        <cfif StructKeyExists(Arguments.GridChanged, "PrjStartDate")>
                             , PrjStartDate = <cfqueryparam cfsqltype="CF_SQL_DATE" value="#Arguments.GridChanged.PrjStartDate#" />
                        </cfif>
                    WHERE
                        ID = <cfqueryparam cfsqltype="cf_sql_integer" value="#Arguments.GridRow.ID#" />
                    </cfquery>
            </cfcase>

[Al_D_]

I have found a solution online for this.  The date that is created is an ISODate Time that needs to be converted to a Coldfusion Date Time.  Here is what I added to my cfc template:

<cffunction
    name="ISOToDateTime"
    access="public"
    returntype="string"
    output="false"
    hint="Converts an ISO 8601 date/time stamp with optional dashes to a ColdFusion date/time stamp.">

    <!--- Define arguments. --->
    <cfargument
        name="Date"
        type="string"
        required="true"
        hint="ISO 8601 date/time stamp."
        />

    <!---
        When returning the converted date/time stamp,
        allow for optional dashes.
    --->
    <cfreturn ARGUMENTS.Date.ReplaceFirst(
        "^.*?(\d{4})-?(\d{2})-?(\d{2})T([\d:]+).*$",
        "$1-$2-$3 $4"
        ) />
</cffunction>

Then for the insert to the database in the Cfquery insert:

<cfquery datasource="#REQUEST.dsn#">
                    INSERT INTO  BookingsInfo(
                        ClientMgr
                        ,ClientMgrUID
                        ,ISU
                        ,Market
                        ,PrjStartDate

                    ) VALUES (
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Session.sn#" />
                        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#Session.UID#" />
                        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridRow.ISU#" />
                        , <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.GridRow.Market#" />
                        , <cfqueryparam cfsqltype="CF_SQL_DATE" value="#ISOToDateTime(Arguments.GridRow.PrjStartDate)#" />
                    )
                    </cfquery>

Hope this helps someone else.

Regards,

Al D.

[Al_D_]

See my answer above.

[-__cfSearching__-]

Glad you solved your problem.  But it's always nice to give the authors of open source code credit for their work 😉

http://www.bennadel.com/blog/811-Converting-ISO-Date-Time-To-ColdFusion-Date-Time.htm

Just my $0.02

[Al_D_]

Yes, it is always good to give credit to the authors. It was late and I was tired and didn't even think of it.

Sorry about that

[-__cfSearching__-]

Yes, it is always good to give credit to the authors.  It

was late and I was tired and didn't even think of it. 

I hear ya' on that one

Cheers