Hi All

I have a application that stores a date in what we believe is UNIX format, when i open the db to view the raw data using either access, sql mysql the date data appears as a 'long interger number'
example of data = 1203213660. however in the application the date appears on screen as 17/02/2008 13.01.
Does anyone have an answer if we can convert this UNIX number to a date/time formatthat access can relate to??

Regards

Darren

This is a common date obfuscation technique that was much used at one time. I think you will find that this code reveals the date for you:

Dim iSecs As Integer
Dim dtDate As Date

iSecs = 1203213660
dtDate = DateAdd("s", iSecs, #01/01/1970 11:00:00#)

The result I got from this code was (USA): 2/17/2008 1:01:00 PM

I would try checking the dates returned against this script, and if they are consistent, use the base date and time, and then use it to display the date as needed, like on the On Format event of a report. Add: Me.DateTextBox = dtDate

I have seen this used in SQL Server applications so it is not unique to UNIX, although the base date may be significant to UNIX.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for your reply,

I tried your code as above and got an overflow error 5, so i modified it to
Me.DateTxt.Value = DateAdd("s", otime, #1/1/1970 10:00:00 AM#)

otime being the Unix time as mentioned in 1st log as value 1203213660

i added this to a command button on a form, when i click the command i now get a AUS date/time format. in the Me.dateTxt field.

the overflow error occured on line (iSecs = 1203213660). Not sure of the reason for this error.

Anyway, i am still waiting confirmation from the user that the date conversion i am using is correct, i will let you know how i get on

Thanks Again, you pointed me in the right direction again

Cheers
Darren

Sorry, the overflow is because I used Interger as the data type. You should use Long since this will hold the values you are likely to encounter. Rookie mistake on my part.


mmcdonal

Look it up at: http://wrox.books24x7.com

Hi mmcdonal,

Just to let you know i tried your code with long and got a result, this has been confirmed with the user as correct, just thought i would let you know.

Cheers

and thanks

Darren