Convert SQL binary Timestamp to Access Date/Time

[Rungo]

Does anyone know how to convert a binary timestamp from SQL Server into something Access can understand and report on? A binary field in an Access table looks empty, like it's coming from a different universe. How can I convert this to the usual mm/dd/yyyy stuff we all know?
- Ken

 

[yamafopa]

Have only done a bit of reading on this but here is what I know/think...

1. The Timestamp datatype is a binary number that reflects the date and time that a record was changed. I do not believe that it is ever bound to the system date. The values simply start at 1 and go up each time an insert or update occurs in the table.
2. I don't think that any version of Access supports this type, so that is why you can't see the stored values.
3. You can Cast the Timstamp values into integers in a T-SQL statement like this...

Select id, Cast(tsTimeStamp as int) As intTimestamp...

... You can try to convert to a DateTime value and you will get a value that looks like a date, but I am pretty sure that date will not represent the system time at the time of the last update or original insert ( if the record has never been updated) of the record.

Hope this is of some help...

yamafopa!

 

[Rungo]

Thank you, this is very informative.

Given that this TimeStamp field is not bound to the system date and time, do you know of any other field used by SQL that would actually store the date and time that a record is added or changed?

 

[yamafopa]

I would guess that this functionality would have to be added by the developer in the form of a DataTime data type field that is updated by code after each insert or update....

yamafopa!

 

[TonyJollans]

Hi Rungo,

I know nothing about SQL Server, but the Timestamp datatype in DB2 is a binary number representing the number of fractions of a second since Adam was a lad. Access Data/Time datatypes are similar but with different units and a different baseline date.

If you know how your SQL Server timestamps can be read, you can convert them. Some years ago ago I wrote some code in Rexx to interpret the DB2 timestamps and I might be able to find it (no guarantees!) and rewrite it in VBA if it would be at all helpful.

Enjoy,
Tony