MSSQL Server - inserting blank in datetime field gives show up as 1/1/1900 12:00:00 AM
Posted on 2011-02-28
I need some help. I've got a form that I created on my home system (SQL Server 2005) - works fine, but when I connected everything and ran some test inserts on my work box (also Server 2005) the dates, unless explicitly assigned a specific date, appear as 1/1/1900 12:00:00 AM.
The form has several optional fields where the registrant selects a skill (cpr, first aid, etc) and then adds a date from a calendar for the certification date. Thus, not all dates are filled. On the back side, I do assign anything blank a NULL but that doesn't work here for some reason - it just inserts the default date.
I can't say I've run into this before and what I've found on other sites hasn't worked - i.e. setting the default field value to null, etc. If I set to NULL, the query works but inserts the 1900 date. If I set it to 'NULL', I get an error about converting string to datetime and so on.
It's not a deal breaker - more of an irritant I'll have to deal with in reporting - so it would be nice to be able to get the fields without dates as null.
Can any one tell me what I'm doing wrong in this instance?
Many thanks in advance.