SQL Server Performance

Vexing Hex Problem

Discussion in 'General Developer Questions' started by AMDer68, Oct 26, 2006.

  1. AMDer68 New Member

    Hello,

    I have a string of charachters that represent hex octets to make up a date. The following string will equate to October 2, 2006 12:00:03

    240902000003

    The first octet, 24 is the hex value for 36. The dates for this particular form of torture begin at 1970 and this represents 36 years after 1970 or 2006. Back to the problem. When I go to create a function in SQL 2000 I separate out the 24, append 0x to the front and convert it to varbinary. I expect the system to come back with 0x24, but no...I get 0x30.

    declare @binYear varbinary(1)
    declare @intYear int
    declare @strYear nvarchar(4)
    declare @strValue nvarchar(50)

    set @strValue = '240902000003'
    --leaving the N out for Unicode does not help...same results
    Set @strYear = N'0x' + Left(@strValue, 2)
    --this prints out '0x24'
    Print 'Year String = ' + @strYear
    Set @binYear = CAST(CAST(@strYear AS nvarchar) AS varbinary(1))
    --this is what I convert
    SELECT CAST(CAST(@strYear AS nvarchar) AS varbinary(1))
    --this is what I want to do
    SELECT CAST(CAST(0x24 AS nvarchar) AS varbinary(1))
    --This is in desperation...
    select Cast(Cast(substring('0x24',1,4) as nvarchar) as varbinary(1))

    So...I run the select statement expecting to see 0x24 and see 0x30. Odd thing is, I seem to get that with any number I use as long as it's in single quotes. '0x1A' = 0x30 and it goes on and on. I know I'm probably missing something simple. Basically I want to get 36 out of 24 hex and this is driving me nuts. Any help would be appreciated. I'm trying to avoid having to write a VB app to convert these logs.
  2. FrankKalis Moderator

    I don't think you're missing anything simple. I don't know the reasons why the following code works, so please don't ask. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />You'll get your desired result, when you execute your statement dynamically. Consider this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @binYear VARBINARY(1)<br />DECLARE @strYear NVARCHAR(4)<br />DECLARE @strValue NVARCHAR(50)<br />DECLARE @stmt NVARCHAR(255)<br /><br />SET @strValue = '240902000003'<br />SET @strYear = N'0x' + LEFT(@strValue, 2)<br />SELECT @stmt = N'SELECT @binyear = CONVERT( VARBINARY(1) , ' + @strYear + ' )' <br />EXEC sp_ExecuteSql @stmt, N' @binyear VARBINARY(1) Out', @binyear OUT <br />SELECT @binyear <br /><br /> <br />---- <br />0x24<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page