?
Solved

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

Posted on 2006-04-06
11
Medium Priority
?
3,796 Views
Last Modified: 2008-02-07
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
0
Comment
Question by:stankstank
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 16390207
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
 

Author Comment

by:stankstank
ID: 16390244
>> 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
 
LVL 15

Expert Comment

by:mcmonap
ID: 16390258
Hi stankstank,

Try entering this as the default value:
('1900-01-01')
I think you just have a format issue
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16390262
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16390266
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
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 600 total points
ID: 16391094
> 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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 16394430
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16394499
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 16394575
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16394896
>>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
 

Author Comment

by:stankstank
ID: 16397488
Thanks for all of the good information guys!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question