Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

yamatojyu (TechnicalUser) (OP)
30 Mar 04 11:10
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 (MIS)
30 Mar 04 12:05
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 (TechnicalUser)
30 Mar 04 12:37
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

Helpful Member!  entaroadun (Programmer)
31 Mar 04 0:38
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 (MIS)
31 Mar 04 8:10
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 (Programmer)
1 Apr 04 10:21
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 (TechnicalUser)
2 Apr 04 8:51
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 (MIS)
2 Apr 04 10:33
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 (Programmer)
2 Apr 04 23:02

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 (MIS)
5 Apr 04 16:24
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):

CODE

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:

vbNull = 1
Saturday, December 31, 1899
Saturday, December 30, 1899
dtVal is Null, VarType: 1


Adding Option Explicit at the head of this script gives us:

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



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 vbNul that isn't caught because Option Explicit is missing.  That, or somebody is otherwise storing a 0 into this field in the database.

-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



The greater error here though is trying to use vbNull as a value for any sort of field at all!  The constant vbNull is strictly meant as a VarType( ) 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 VbVarType in the VBA library.

When you want to store "null" data, you use Null 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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close