SQL Server Binary TimeStamp

greg · Post by **greg** » Tue Aug 15, 2006 4:50 pm

I'm trying to select a timestamp from SQL Server using ODBC connection, the Table Def has the field as Binary and so it returns the value 30303030303030303031463635444644, if I put it through OCONV I get 7481BC-08-12 -03:00:44. But if I change the SQL Type to integer, the Length to 10 and the Data Element to SQL.ROWVERNUM or SQL.BINARY8 I get 2108-06-22 01:18:53. I do not really know what the true value in the source table is yet but I expect that neither return values are correct.

Is there something I'm missing?

Thanks,

chulett · Post by **chulett** » Tue Aug 15, 2006 5:16 pm

When you say 'Table Def' do you mean the imported metadata or the actual table definition in the database? I've never dealt with anything like a 'Binary Timestamp' but I would think someone there that you work should be able to help you determine if you are getting valid values for any particular row. I don't know how we can know if either of those values is the "true value".

Also curious what the actual OConv conversion code is that you are using.

DSguru2B · Post by **DSguru2B** » Tue Aug 15, 2006 10:03 pm

If its coming in as a binary format then i would guess that an ascii conversion is needed before you do any other sort of manipulation. Thats why we need to see your OCONV code as Craig requested to make sure you are doing the binary to ascii conversion.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 15, 2006 11:39 pm

What specification did you use with Oconv()?
Try Iconv() with "MX0C" as the conversion, see what you get with that. It's probably an ordinal date/time, so you will then need to convert the total seconds into days (then convert with Oconv() to a date) and seconds (then convert with Oconv() to a time) and re-assemble as a timestamp.

greg · Post by **greg** » Wed Aug 16, 2006 10:01 am

OK so I can stop pulling out my hair, "Thanks Bill Gates and MicroSoft". I talked to my DBA and they sent me the following.

It is not about the time the row was created or updated. Instead, it is a sequence number. Here is some more information:

timestamp

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms. Each time a page was modified, it was stamped with the current @@DBTS value and @@DBTS was incremented by one. This was sufficient for recovery to determine the relative sequence in which pages had been modified, but the timestamp values had no relationship to time.

In SQL Server version 7.0 and SQL Server 2000, @@DBTS is only incremented for use in timestamp columns. If a table contains a timestamp column, every time a row is modified by an INSERT, UPDATE, or DELETE statement, the timestamp value in the row is set to the current @@DBTS value, and then @@DBTS is incremented by one.

Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

To record the times data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers to automatically update the values when any modification takes place.

ray.wurlod · Post by **ray.wurlod** » Thu Aug 17, 2006 7:51 am

Seems to be related to that other SQL Server idiosyncracy, the GUID (guaranteed unique ID). Can you ask your DBA whether it's the same? (I'm just curious, having solved GUID issues with DataStage in the past. You can search the forum for those solutions.)

ray.wurlod · Post by **ray.wurlod** » Thu Aug 17, 2006 7:52 am

Seems to be related to that other SQL Server idiosyncracy, the GUID (guaranteed unique ID). Can you ask your DBA whether it's the same? (I'm just curious, having solved GUID issues with DataStage in the past. You can search the forum for those solutions.)