TimeStamp from string ?

Question

Post reply

TimeStamp from string ?

sholliday

SSCrazy

Points: 2100
More actions
June 30, 2006 at 11:21 am

#118532

I am passing xml into a stored procedure.
So I can handle 1 or more database updates in a single call. (I already
know the pros and cons of this).
..
Well, I'm trying to work in some concurrency checking with the TimeStamp
method.
( the table has a definition for a timestamp, lets call it emptimestamp )
I pass the time stamp value up to the presentation layer. Because I'm in a
web environment, I'm using a hidden tag, and converting the timestamp to a
string.
ex:
00-00-00-00-00-00-06-B7
I'd like to convert this back into a timestamp .. ~~inside the tsql code.
and use the value to check for concurrency.
since i'm using xml, i have to pass it in as an element
<xml>
<employees>
<employee>
    <empid>123</emp>
    <lastname>smith</lastname>
    <empTS>00-00-00-00-00-00-06-B7</empTS>
</employee>
<employee>
    <empid>234</emp>
    <lastname>jones</lastname>
    <empTS>00-00-00-00-00-00-06-BA</empTS>
</employee>
</employees>
Anybody know how to convert that stringByte .. back into a timestamp...
~with tsql code??
PS
I'm still in sql server 2000.
Thus .Net code inside tsql is not possible yet.
..
My code to convert the timestamp to a string is:
private string ConvertToString(byte[] ts)
{
return BitConverter.ToString ( ts );
}

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

create

function dbo.fn_char16tobin8 (@hexstr as char(16))

returns

binary(8)

as

begin

if @hexstr is null return null

if @hexstr like '%[^0-9a-fA-F]%' return null

declare @bin binary(8), @byte int, @varbin varbinary(8)

select @byte = 8

select @varbin = dbo.fn_char2tobyte(substring(@hexstr, (@byte * 2)-1, 2) )

select @byte = @byte -1

while @byte > 0

begin

set @varbin = dbo.fn_char2tobyte(substring(@hexstr, (@byte * 2)-1, 2) ) + @varbin

-- binary '+' concatenates (not adds!) a pair of digits at a time

-- to the left of the string.

set @byte = @byte - 1

end

select @bin = @varbin

return @bin

end

go

create

function dbo.fn_char2tobyte(@char2 char(2))

returns

binary(1)

as

begin

declare @bin binary(1)

set @bin = cast(case substring(@char2,2,1)

when '0' then 0x00

when '1' then 0x01

when '2' then 0x02

when '3' then 0x03

when '4' then 0x04

when '5' then 0x05

when '6' then 0x06

when '7' then 0x07

when '8' then 0x08

when '9' then 0x09

when 'a' then 0x0a

when 'b' then 0x0b

when 'c' then 0x0c

when 'd' then 0x0d

when 'e' then 0x0e

when 'f' then 0x0f

end

|

case substring(@char2,1,1)

when '0' then 0

when '1' then 16

when '2' then 32

when '3' then 48

when '4' then 64

when '5' then 80

when '6' then 96

when '7' then 112

when '8' then 128

when '9' then 144

when 'a' then 160

when 'b' then 176

when 'c' then 192

when 'd' then 208

when 'e' then 224

when 'f' then 240

end

as binary(1))

return @bin

end

GO

declare

@char char(16)

select

@char = replace('00-00-00-00-00-00-06-BA','-','')

select

dbo.fn_char16tobin8(@char)

go

drop

function dbo.fn_char16tobin8

go

drop

function dbo.fn_char2tobyte

But you might find it easier to convert the binary value to an integer to start with since you can then straightforwardly CAST() it back to binary(8) in TSQL. An alternative would be to to build a binary literal (0x00000000000006BA) then use sp_executesql to run a CAST() to binary and return the output.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

sholliday SSCrazy Points: 2100 More actions · Answer 2

Wow! Thanks Tom for the udf. Its perfect.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Here is another approach, which I also believe as faster than Tim's suggestion. Preliminary testing shows that it is 4 times faster.

CREATE FUNCTION dbo.fnTs2Bin8

(

@TS VARCHAR(23)

)

RETURNS BINARY(8)

AS

BEGIN

DECLARE @Bin8 BINARY(8)

IF UPPER(@TS) LIKE '[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]-[0-9A-F][0-9A-F]'

SELECT @Bin8 =

CONVERT(BINARY(2), 4096 * (CHARINDEX(SUBSTRING(@TS, 1, 1), '0123456789ABCDEF') - 1) +

256 * (CHARINDEX(SUBSTRING(@TS, 2, 1), '0123456789ABCDEF') - 1) +

16 * (CHARINDEX(SUBSTRING(@TS, 4, 1), '0123456789ABCDEF') - 1) +

(CHARINDEX(SUBSTRING(@TS, 5, 1), '0123456789ABCDEF') - 1)) +

CONVERT(BINARY(2), 4096 * (CHARINDEX(SUBSTRING(@TS, 7, 1), '0123456789ABCDEF') - 1) +

256 * (CHARINDEX(SUBSTRING(@TS, 8, 1), '0123456789ABCDEF') - 1) +

16 * (CHARINDEX(SUBSTRING(@TS, 10, 1), '0123456789ABCDEF') - 1) +

(CHARINDEX(SUBSTRING(@TS, 11, 1), '0123456789ABCDEF') - 1)) +

CONVERT(BINARY(2), 4096 * (CHARINDEX(SUBSTRING(@TS, 13, 1), '0123456789ABCDEF') - 1) +

256 * (CHARINDEX(SUBSTRING(@TS, 14, 1), '0123456789ABCDEF') - 1) +

16 * (CHARINDEX(SUBSTRING(@TS, 16, 1), '0123456789ABCDEF') - 1) +

(CHARINDEX(SUBSTRING(@TS, 17, 1), '0123456789ABCDEF') - 1)) +

CONVERT(BINARY(2), 4096 * (CHARINDEX(SUBSTRING(@TS, 19, 1), '0123456789ABCDEF') - 1) +

256 * (CHARINDEX(SUBSTRING(@TS, 20, 1), '0123456789ABCDEF') - 1) +

16 * (CHARINDEX(SUBSTRING(@TS, 22, 1), '0123456789ABCDEF') - 1) +

(CHARINDEX(SUBSTRING(@TS, 23, 1), '0123456789ABCDEF') - 1))

RETURN @Bin8

END

N 56°04'39.16"
E 12°55'05.25"

stax68 SSChampion Points: 11711 More actions · Answer 4

Excellent cleanup job, much better for large recordsets, though not so generic. Your time has been well spent. It would be a good idea to add a comment when you rewrite a post though.

[edit: and watch out for case-sensitive collations!]

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

sholliday SSCrazy Points: 2100 More actions · Answer 5

I think the cast as int might be the way to go... after working with it so long.

Thanks for all input.

stax68 SSChampion Points: 11711 More actions · Answer 6

Good decision. First rule of cost-benefit analysis: the past doesn't exist.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant