MSSQL Server - inserting blank in datetime field gives show up as 1/1/1900 12:00:00 AM
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nevertheless, I was realized that the dates was really just text and would never be treated as a real date for purposes of comparison or anything. With that caveat, I just changed the data type and finished up. Not the best of solutions but it works for my purposes at the moment. Now that the heat is off, I can spend more time exploring this with our dba.
Many thanks to all the suggestions and examples.