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