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?
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?
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.
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.
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.
yourdate = createdate()
yourdate = defaultvalue
Then use the yourdate variable in your query.