History of 12/30/1899 1

[yamatojyu]

Sorry if I sound like a complete newbie, but I'm trying understand how 12/30/1899 was established as the default null date. From what I've read most databases use this date, but I was wondering why and what is the history behind that. Any information would be greatly appreciated.

 

[johnherman]

I don't understand what you mean about default null date. Defaults and Nulls are two important, but separate concepts. If a particular application is using 12/30/1899 as their default null date, then they must have a reason. I certainly would not use that date for a default Date of Birth. For instance, there were no aircraft, automobiles, or televisions before 1899, so using that date as the default for "aircraft last inspection date", "automobile insurance effective date", or "last Screen Actors Guild union dues payment date" would seem reasonable.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[MDXer]

I think what yamatojyu is asking about is that in probably some but I know sql server if you pass a time into a datetime column it will append a "Default" date to it.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[entaroadun]

The concept only applies to RDBMS implementations that store date/time values as double-length floats. For SQL Server, the value 0 signifies 1/1/1900 12:00 AM, or literally, the first moment in the year 1900. At least I think it's 1900... where did you find the 12/30/1899 value?

Oracle stores date/time values with integers reflecting the separate fields, i.e. year, month, day, etc. This doesn't apply.

As for why... who cares? The RDBMS hides the implementation from you anyways. The first implementations were in the 1970s, and the programmers had to pick some point in time to be 0. 1970 would have seemed too decade-centric. 2000 would have all time values from the 1970s onwards stored as negative values. 1 AD or 1000 would cause precision to drop; time is a floating point, and the farther away you get from zero, the less precise the value. 1900 would be a good place.

 

[johnherman]

What is your database? Actually, MS SQL Svr supports smalldatetime and datetime. Smalldatetime is for "recent" dates. Datetime must be used for old dates like birthdate. The difference is in the storage requirements and range of dates available. I don't have a reference to quote you them right now....

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[Slarti]

I'm using Access with VB and concur with yamatojyu. If I want to clear a date field I can't use !DateField = vbNull as this sets the DateField to 12/30/1899. What do I do to empty the DateField.

Getting Balder!

 

[blom0344]

We experienced the 12/30/1899 issue too when we started to extract data from Borland's Interbase RDBMS.
It is no pain if it is a constant value .........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[johnherman]

As T.Blom states, if it is constant, it should not be a big problem. However, there are porbably still people alive today who may have been born on 12/30/1899, so Access is actually wrong to use that date as a default. Especially for any system that keeps history. Certainly, records from 20 years ago will have someone with that birthdate.

What you could do is when reading Access records in VB, use a CASE statement to assign null when the date is 12/30/1899. When going the other way, Access already converts the null to 12/30/1899 as you stated.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[tdatgod]

Hi,
0 means 1/1/1900 12:00 AM, but at Who's Timezone.

With a -8 TZ ( PST ) 0 would mean 12/31/1899 4:00 PM.


Although that still doesn't explain 12/30/1899 unless the TZ is set to -25 or lower.


---

 

[dilettante]

My take on this is that we're dealing with a combination of "right church, wrong pew" and a VB coding error exacerbated by a bad practice.

Consider the following VBScript (yes, I know, but the rules are basically the same as in VB):

Dim dtVal

MsgBox "vbNull = " & CStr(vbNull)

dtVal = CDate(vbNull)
MsgBox FormatDateTime(dtVal, vbLongDate)

dtVal = CDate(vbNul)
MsgBox FormatDateTime(dtVal, vbLongDate)

dtVal = Null
If IsNull(dtVal) Then
  MsgBox "dtVal is Null, VarType: " & CStr(VarType(dtVal))
Else
  MsgBox FormatDateTime(dtVal, vbLongDate)
End If

Executing this with wscript.exe gives us the results:

[tt]vbNull = 1
Saturday, December 31, 1899
Saturday, December 30, 1899
dtVal is Null, VarType: 1[/tt]

Adding [tt]Option Explicit[/tt] at the head of this script gives us:

[tt]vbNull = 1
Saturday, December 31, 1899
Variable is undefined: 'vbNul' Code: 800A01F4[/tt]


So the first issue is that CDate(vbNull) yields 12/31/1899 and not 12/30/1899.

I can only assume the 12/30/1899 date is being arrived at through a typo such as [tt]vbNul[/tt] that isn't caught because [tt]Option Explicit[/tt] is missing. That, or somebody is otherwise storing a 0 into this field in the database.

[tt]-657434.0 == Midnight 01/01/0100, local time
-1.0 == Midnight 12/29/1899, local time
0.0 == Midnight 12/30/1899, local time
1.0 == Midnight 12/31/1899, local time[/tt]


The greater error here though is trying to use [tt]vbNull[/tt] as a value for any sort of field at all! The constant [tt]vbNull[/tt] is strictly meant as a [tt]VarType( )[/tt] constant, used to identify what sort of data type is stored in a given Variant variable. It has a simple value 1 and is a member of the Enum [tt]VbVarType[/tt] in the VBA library.

When you want to store "null" data, you use [tt]Null[/tt] instead.


Access shares the same Date type with VB/VBA/VBScript. SQL Server has its own DATETIME format and others, and other DBMSs use still different representations. Each system has its own methods of storing "missing" or "null" values. Then you need to consider how you are accessing the data store itself. ODBC drivers may do one transformation, a native OLE DB provider yet another.