Solved

1900 vs 1899 Date Problem in Access Project

Posted on 2006-06-14
6
606 Views
Last Modified: 2012-06-27
I have an Access Project against a SQL Server backend. When I execute a command to enter a time into a DateTime field it sometimes shows with a date of 01-Jan-1900 and sometimes with 30-Dec-1899. This means that if you try to sort based on the time part only it puts all the 1899's first!

So if I:

DoCmd.RunSql "UPDATE Table SET MyTime='12:23'"

And then query the field in QA I usually get:

'01-01-1900 12:23' but now and again will get '30-12-1899 12:23'

Help!!!
0
Comment
Question by:dr_dudd
6 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 16904263
If you do not like the base date, than try adding a date as in:
DoCmd.RunSql "UPDATE Table SET MyTime='2006-06-14 12:23'"
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16904656
Or...

When you create the date field, set a default value in the table definition. This will insure that all new records have the same base date.

If you want the date that the record was created as the default, enter =GetDate() in the Default Value box in your Enterprise Manager table designer.

M@
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16905320
show us your order by clause please...

if you order by time it should look like...

order by Convert(DATETIME,MyTime,8)

AND AGAIN ... YOUR SELECT should only display the "time" portion...
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:dr_dudd
ID: 16906011
The problem is to do with Access defaulting its first date to 31-Dec-1899 (I have no idea why it always shows as 30-Dec and not 31-Dec) vs. SQL Server on 01-Jan-1900. The SQL statement is in VBA but DoCmd.RunSQL surely would get the server to execute the command as T-SQL and not JET SQL and (I would have thought) always default to 1900-01-01 as SQL Server is meant to do.

The ORDER BY is on the form in Access, so I can't use T-SQL commands. Setting a default value on the table might work, but I would have thought that it would simply be over-written by the 1900 or 1899 date when the user enters the time.

ACPERKINS idea is probably the safest - force the date. I will try to force it to 1900-01-01 for everything and see if that fixes it. The problem is that most times it uses 1900-01-01 anyway, it is just every now and then I get an 1899-30-12 for no apparent reason - so determining that the problem is solved might be tricky!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16907302
>>I will try to force it to 1900-01-01 for everything and see if that fixes it. <<
That is what I would do.
0
 
LVL 2

Author Comment

by:dr_dudd
ID: 16907688
Success so far. I have taken a set of records that were in 1899 and then re-saved them using the new code and they are all saying 1900-01-01. Thanks for your help.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
Creating SQL script using SQL data and SQL script 8 40
This query failed in sql 2014 5 31
transaction in asp.net, sql server 6 33
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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