What values can a datetime data type hold other than date and time??

Hi folks,

I have designed a webpage using asp, i have a text box with a calender in the page from which the user will select a date. since the date field is not mandatory, if this field is left un-entered/blank then the SQL server will automatically inserts the default date "1/1/1900" while insertion to the Database.

I am trying to eliminate/replace the default date getting stored to the database while the date field left blank (Note: Per requirement i cannot make the date field manadatory).

So i am trying to write a condition to check if the date field is empty and if YES then replace some value, before the code inserts the form values to the Database.

if date_field = "" then
date_field = ""
end if

The above code still inserts dafault since the datetime data type itself will not allow blank but if i say date_field = 0 then the system errors out since the data type is datetime. Please help with this issue.

Thanks in advance

Regards,
chandru

re: What values can a datetime data type hold other than date and time??

Options:
1. On your front-end (ASP) check if the object value is empty. If it is, store NULL.
2. On the database, use NULL as default value of the column on your table.

Either case, you need to allow NULL value to be stored in the column.

Happy coding!!!

--- CK

re: What values can a datetime data type hold other than date and time??

Hi Ck,

Thanks for the reply. I understood your first point.

In your second point i have a slight re-confirmation to make so that we both are on the same page.

In your second point of allowing NULLS, do you mean to change the property of the column from not null to null so that accepts Null values... Please advise.

Also if there is any way? that we can make SQL server not to automatically stores its default values. Thanks in advance.. waiting for your response

regards,
Chandru

re: What values can a datetime data type hold other than date and time??

Yes.

If you specify a default value on the column of a table, it will store that default if you don't specify any value that will be stored to it (ie, not included on the column list on the INSERT INTO clause). If you specify a value, it will store the value you specified, not the default value.

To avoid storing the default, always include it on your INSERT INTO column list and specify a value on the SELECT or VALUES clause.

Happy Coding!!!

--- CK