Default datetime sets to 1/1/1900 12:00 AM - Why?

Hi Experts,

I am using MSSQL Server 2005 to store information.  I have two columns which are date/time datatypes.  Users are allowed to keep the date blank in my application, effectively putting a NULL value in my database where the date should go.  Instead of a NULL, I want to have a date that is obviously a NULL replacement (for indexes/performance reasons).  

In the 'Default Value or Binding' option for my column, I entered '1/1/1995' (without the quotes).  When I saved the changes, the database saved it as '(((1)/(1)/(1995))' (again, without the quotes). When I enter a new record without a date '1/1/1900 12:00:00 AM' comes up instead of '1/1/1995 12:00 AM'.  It is okay with me that it does this, I just need/want to know why - I don't understand it.  SQL server is accomplishing my task for me, but I want to understand what is going on here.

Thanks alot,

StankStank
stankstankAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
as soon as you specify a value for a field, the default value is ignored.
if you only specify a time portion, the default date 1900/01/01 is used.

If you want to put a default date instead of 1900, you have to create a trigger on the table that changes the default date of 1900/01/01 by the date you want to have.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stankstankAuthor Commented:
>> as soon as you specify a value for a field, the default value is ignored.
if you only specify a time portion, the default date 1900/01/01 is used. <<

The thing is, I am not specifying a value for the field.  It stays null until I refresh the table.  

>> If you want to put a default date instead of 1900, you have to create a trigger on the table that changes the default date of 1900/01/01 by the date you want to have. <<

I don't want a default date other than 1900, that's fine... I want a default date other than NULL - that's all I care about.  How do I accomplish this the appropriate way?  Entering a date in the default value field changes it to 1900 - so am I going about this the right way?  Is this slowing down my database?
0
mcmonapCommented:
Hi stankstank,

Try entering this as the default value:
('1900-01-01')
I think you just have a format issue
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see now that you actually entered a wrong value for the default, as it is actually stored as numerical expression (division of 1 by 1 divided by 1995), this numerical expression is then evaluated as date/time fraction which is the 1900/01/01 ...

to correctly put a date default:

convert(datetime, '1995-01-01', 120)
0
Aneesh RetnakaranDatabase AdministratorCommented:
try this out

declare @tab table( i int identity, j int, k datetime)
insert into @tab SELECT 1,''
insert into @tab SELECT 1,NULL

SELECT * from @tab
0
Vadim RappCommented:
> I just need/want to know why - I don't understand it.  

> In the 'Default Value or Binding' option for my column, I entered '1/1/1995' (without the quotes).  When I saved the changes, the database saved it as '(((1)/(1)/(1995))' (again, without the quotes).

witout the quotes sql server understands this as an expression: 1 divided by 1 divided by 1995. It inserts parenthesis to make the calculation order without a doubt.

> When I enter a new record without a date '1/1/1900 12:00:00 AM' comes up instead of '1/1/1995 12:00 AM'.  

Datetime values are saved internally in the database as real numbers, with the whole part being the date and fraction being the time. When you are saving only time, it becomes 0.something ; when it's translated back to the date, zero is translated to the sql server initial date which is 1/1/1900.

0
Anthony PerkinsCommented:
vadimrapp1,

>>Datetime values are saved internally in the database as real numbers, with the whole part being the date and fraction being the time. <<
I think you are confusing this with MS Access which uses a double for datetime columns.  MS SQL Server uses two 4-byte integers for datetime and two 2-byte integers for smalldatetime.
0
Vadim RappCommented:
acperkins,

select cast (0.7 as datetime)

result: 1900-01-01 16:48:00.000

select cast (0.8 as datetime)

result: 1900-01-01 19:12:00.000
0
Vadim RappCommented:
I see what you mean though, by looking in BOL; but obviously, that's how the conversion works - even though the time is stored as additional 4-byte integer, during the conversion it actually works as the fraction.
0
Anthony PerkinsCommented:
>>I see what you mean though, by looking in BOL<<
Yep.  Thats exactly what I meant.

>>during the conversion it actually works as the fraction.<<
Correct.  There is an implicit conversion of a numeric fraction to a fraction of a date.
0
stankstankAuthor Commented:
Thanks for all of the good information guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.