datefield in <cfform> inserts 01/01/1900 when left blank.

[jenn1]

Hi,

Does anyone know why <cfinput type="datefield" name="date" size="10"> inserts 01/01/1900 when the field is left blank?  In my insert statement, I can see that a blank  " " is inserted.  When I query the record I get 01/01/1900.

Has anyone else run into this issue?

Thanks,

Maryam

[Jochem van Dieten]

If I were to guess I would say your database converts an empty string to the number one and ColdFusion reads that number back as day one in the year 1900. What is that datatype you are inserting into?

[jenn1]

The datatype is datetime.

[ilssac]

Is there any type of "default" value defined for that field.

I suspect you are getting either a defined or system default value stored in the table since no value was supplied that the table could accept.  A single space character is not a "datetime" type value.

[jenn1]

Thanks for everyone's replies.  I modified the field in the database to accept null values for

datetime.  Sybase was kindly creating a date string if the field was passed to it blank.

[BKBK]

Like others, I think the database inserts 01/01/1900 by default when you insert the empty string " " or other non-date values, like integers. I also think configuring the database to set the value in the column to null won't solve the problem. The database engine would use null only when no data is inserted.

When the value " " or some such comes in, the database engine thinks, "Ah, a value has come in, so no need to insert the null". The result is the default  01/01/1900.

One answer is to check, before the insert query, whether the value is a date. Remember there are idiosyncrasies in Coldfusion that don't carry over to databases, like isDate("1a") returning 'yes'. So, before inserting, also check whether the format is compatible with that of the database column. If not, insert a null.

[Dan_Bracuk]

Regarding - So, before inserting, also check whether the format is compatible with that of the database column. If not, insert a null.

If you have a predictable format instead of simply checking, something like this is a bit more proper.

<cftry>

yourdate = createdate()

<cfcatch>

yourdate = defaultvalue

Then use the yourdate variable in your query.

[TLC-IT]

(shrug...)  I tell ColdFusion that the incoming parameter is a "string" and do all of the necessary data-conversions "by hand" inside my function.