Date/Time appearing as long Integer

[John D via AccessMonster.com]

I am working on a access table which is link with some external software (3rd
party software).
Everything works fine but the date/time field is appearing as a long integer
value i.e. 1132675652 or 1132674975 etc. I want to convert this numerical
value to some sensible date and time format. I would really appreciate if
someone can advise how to do this.

 

[Ed Robichaud]

Access date/time fields are a number (serial long integer from 1800, I
believe). Use the Format property ( in table design) to set how that
field's value displays - Short Date, Medium Date, etc. BTW, the Access Help
has a good discussion of date/time and other field formats.
-Ed

 

[fredg]

I am working on a access table which is link with some external software (3rd
party software).
Everything works fine but the date/time field is appearing as a long integer
value i.e. 1132675652 or 1132674975 etc. I want to convert this numerical
value to some sensible date and time format. I would really appreciate if
someone can advise how to do this.

Access stores Date and Time values as a Double (not Integer) datatype.

Today's date and time (as of 1:55 PM) is stored as
38678.5800462963
38678 is today's date while the decimal is the time value (as a
percentage of 24 hours).

Unless you can better explain how your numbers represent a date i
don't think there is any way to help you.

 

[Ed Warren]

Step one is to figure out the 'code' the third party software is using to
store dates
E.g. 1132675652 --> Date 1 ---> Access date value zzzzz.aaaaaa
1132674975 --> Date 2 --> Access date value xxxx.yyyyyyy
What math conversion will take the value from the stored value to the
desired value??

Then convert those dates to the Ms Access format using your query
Then how to show those dates in the format you want.

Lots of luck

Ed Warren.

 

[John Spencer]

If you know what date and time is represented by a number then you may be able
to work out the date and time. It could be the number of seconds from some zero
date or it could be the number a milliseconds from some zero date or >>>

In other words you need to know some more about the third party software.

For instance, if the start date was Midnight of January 1, 1970. Then
DateAdd("s",1132675652,#1/1/1970#) would become 11/22/2005 4:07:32 PM

 

[John D via AccessMonster.com]

Bundle of thanks. Yes, it works for me.

If you know what date and time is represented by a number then you may be able
to work out the date and time. It could be the number of seconds from some zero
date or it could be the number a milliseconds from some zero date or >>>

In other words you need to know some more about the third party software.

For instance, if the start date was Midnight of January 1, 1970. Then
DateAdd("s",1132675652,#1/1/1970#) would become 11/22/2005 4:07:32 PM