Access Date Field

[MrsNic]

I have inherited a very old database that died, but I have managed to retrieve the data. Only one problem

The date field is stores as a number (from 1/1/1970) I need to find out which date each particular number refers to.

I have tried the obvious converting it to a date field. I also tried getting the difference between the date today and the date in 1970 (my maths might have been wrong here) but it didn't work.

Many thanks

Cath

 

[RoyVidar]

Is that the Unix startdate?

That date, in the Access world, should be (hit ctrl+g and try)

[tt]? format(#1970-01-01#, 0)[/tt]

- should give 25569 (difference between 1/1/1900 and the above), which you should be able to add to the current number (+/-1?), then (+10 000 days - your number)

[tt]? format(35569, "yyyy-mm-dd")[/tt]

So basically, I think you should be able to add the difference to your date, then format it as a date. Not having tried it, I's suggest you play a little with an update query, on a copy of the db, of course, and see if this gets you any closer.

Roy-Vidar

 

[MrsNic]

Hi Roy

Thanks for your help, I did what you suggested but all the records (over 2000) can up with the same date 19/05/1997? The dates should run from sept 2005

I have also just noticed that the number is 'time in seconds from 1/1/1970'

Sorry to be a pain but we need the date to be able to bill pupils for printouts made.

Cath

 

[4946]

Hi MrsNic,
If your datefield is a 10-digit number.....
Try

DateAdd("s",(yourtabledatevalue),#1/1/1970#)

If the table stores the value as a 10-character string try

DateAdd("s",val(yourtabledatevalue),#1/1/1970#)

You may use

Select DateDiff("s",#1/1/1970#,(now()))

to see the current date and time in UNIX time's designation in seconds (a 10-digit number).

Use

Select DateDiff("s",#1/1/1970#,(([Enter MM/DD/YYYY HH:NN:SS]))),DateAdd("s",(yourtabledatevalue),#1/1/1970#)

to manually test with your table values.

If the table's values are in GMT time the GMT offset for your time zone will need to be subtracted from the table's value

DateAdd("s",(yourtabledatevalue - yourtimezoneoffsetinseconds),#1/1/1970#)

Hope this helps.
WinN