Date insertion problems MySQL

[Buddhatech74]

I am currently trying to update a mysql database with a date entered using a
form. However when I look at the database the date always reads 1/1/0001.

I tried locally with debugging on and the date is getting submitted as fom
data. Look like this 03/09/2005


Here is my code:

<cfquery datasource="dumped">
UPDATE dumped SET dumpdate=
<cfif IsDefined("FORM.dumpdate") AND #FORM.dumpdate# NEQ "">
#FORM.dumpdate#
<cfelse>
NULL
</cfif>

Any help would be great!

[philh]

Budd, Check out the function CreateODBCDateTime(). It may help you here.
Also, if FORM.dumpdate is in the format mm/dd/yyyy or dd/mm/yyyy, surround it
with single quote marks (or, for Access, the dreaded pound sign). HTH,

[Buddhatech74]

Ok So I took some of your suggestions and I think I am closer. Thank you. Here
is my code now:

<cfquery datasource="dumped">
UPDATE dumped SET dumpdate=
<cfif IsDefined("FORM.dumpdate") AND '#FORM.dumpdate#' NEQ "">
#DateFormat(CreateODBCDate(FORM.dumpdate), "dd/mm/yyyy")#

<cfelse>
NULL
</cfif>

and here is whats being submitted:

SQL Queries

(Datasource=dumped, Time=0ms, Records=0) in
C:\Inetpub\wwwroot\dumped\dumpeeinfo.cfm @ 14:02:24.024

UPDATE dumped SET dumpdate=

22/03/2005


, besttime=

NULL

, customermessage=

NULL

Keep in mind the date is coming from one field in my form.

Thanks for any help I can get.

[philh]

Yup, almost there. <cfquery datasource='dumped'> UPDATE dumped SET dumpdate=
<cfif IsDefined('FORM.dumpdate') AND '#FORM.dumpdate#' NEQ ''>
#DateFormat(CreateODBCDate(FORM.dumpdate), 'dd/mm/yyyy')# <cfelse> NULL
</cfif> Now, surround the date value with single quotes: <cfquery
datasource='dumped'> UPDATE dumped SET dumpdate= <cfif
IsDefined('FORM.dumpdate') AND '#FORM.dumpdate#' NEQ ''>
'#DateFormat(CreateODBCDate(FORM.dumpdate), 'dd/mm/yyyy')#' <cfelse> NULL
</cfif> I don't know what your flavor of DB accepts for date delimiters, so
YMMV. HTH,