André Krijnen

Timestamp in SQL Server:

1) 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.

2) It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

For a customer we made some changes. And the situation is as follows. We exported the data to excel. Multiple tabels are exported. We changed the data in the excel file and the customer validated the data. We also did export the timetamp with the data. Why? Because we had to import it again to the database. If a Timestamp changed, we should not update the data from the excel file. That’s why.

Problem situation:

Because a timestamp isn’t a varchar, or numeric value, but a binary(8) value we had to convert it. Because we import the data by SSIS back into the database. But the data we imported is from a flat text file. So every column was read as a DT_STR by SSIS, and timestamp in the stored procedure was a datatype of Timestamp. So we did a comparison of a varchar against a timestamp. Well that situation didn’t work out that well.

So what we did was to change the parameter of the stored procedure to varchar. Well SQL Server can’t handle a comparison of varchar against timestamp inside the stored procedure. Of course, so we did try it to convert it to a varbinary(8) or binary(8) and then validate it against the timestamp. Well that didn’t work out that well.

declare @timestamp varbinary(8)
set @timestamp = CONVERT(varbinary(8), '0x0000000001F4AD88')
print @timestamp

returned: 0x3078303030303030

declare @timestamp varbinary(8)
set @timestamp = CONVERT(varbinary(8), 0x0000000001F4AD88)

print @timestamp

returned: 0x0000000001F4AD88

That comparison didn’t work well. So I had to figure an other way to validate on timestamp. When I almost lost my hope I wrote a function in SQL Server.

create function [dbo].[sp_hexadecimal] ( @var varbinary(255) )
returns varchar(255)
as

begin
      declare @charval varchar(255)
      declare @i int
      declare @length int
      declare @hex char(16)

      select @charval = '0x'
      select @i = 1
      select @length = datalength(@var)
      select @hex = '0123456789abcdef'

      while (@i <= @length)
      begin
            declare @tempint int
            declare @firstint int
            declare @secondint int

            select @tempint = convert(int, substring(@var,@i,1))
            select @firstint = floor(@tempint/16)
            select @secondint = @tempint - (@firstint*16)
            select @charval = @charval +
            substring(@hex, @firstint+1, 1) +
            substring(@hex, @secondint+1, 1)

            select @i = @i + 1
      end

return ( @charval )
end

So what I did was the other. I managed to validate the data on the other way.

it did the trick...