Solved

1/1/1990 and 12/30/1899 default dates

Posted on 2000-05-01
8
815 Views
Last Modified: 2012-08-14
When using a script, or the a dll, to add a new record to the db. Dates are automatically defaulted to 12/30/1899 when only time is specifed. So, for instance if i have code:
dbrst("dttime") = time()
Then it will automatically append 12/30/1899 in there.
But the default date for the database is 1/1/1990. So, I've been having date comparision problems.. Is this a known problem? Could it be our ado? We have the latest version.

I'm really stumped. Please help.
0
Comment
Question by:thyd
8 Comments
 
LVL 1

Expert Comment

by:manchula
ID: 2768035
This is not your ADO problem. Why don't you try this?

dbrst("dttime") = Date() + Time()
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2768612
I confirm that you should add the date part manually.
In fact, the 1/1/1990 corresponds to
31/12/1899 24:00. While supplying a time, only that part is replaced, and you end up with the 31/12/1899 as date.
0
 

Author Comment

by:thyd
ID: 2770051
Sorry, that's not what i'm looking for.
It's because in sql online help it states that the default is 1-1-1900.
And when using addnew function it appends 12-31-1899. But when using the insert function, or update function it appends 1-1-1900.

Please help.

Thanks..
0
 
LVL 6

Accepted Solution

by:
Jon_Raymond earned 100 total points
ID: 2771940
SQL Server uses a different numeric base to store dates than VB does.  Dates are actually stored as integers.  In VB the base 0 equates to 12/31/1899 00:00:00, in SQL 0 equates to 1/1/1900 00:00:00.  In VB today's date converts to 36649; in SQL it's 36647.  I think this is the root of the problem.  If one procedure uses SQL processes to compute the date and another uses VB then the discrepancy will occur.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2771947
Correction: the VB 0 base is 12/30/1899 00:00:00
0
 

Author Comment

by:thyd
ID: 2774142
Jon,

Is there a way to solve the problem?


Thanks...
0
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2775878
Good question.  I don't have a cover-all answer.  When I last delt with this I deliberately used 12/30/1899 as the zero date value, because all blank dates were appearing as that value.  So I queried for dates that were > 12/30/1899 instead of dates that were not null.  I haven't seen the situation you describe, but you probably have to deal with each circumstance.  You might be able to add 1/1/1990 to the time value, for example.  It depends on the situation.
0
 

Author Comment

by:thyd
ID: 2777861
I'll accept your first comment, as an answer. We really didn't want to append extra values to the time.

Thanks for answering.

Thy
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now