November 13, 2002 at 9:47 am
Hello,
I need to convert a packed decimal to a date... Unfortunately it is stored that way on a mainframe and then moved over to SqlServer. Any ideas???
November 13, 2002 at 10:06 am
By packed decimal I'm guessing you mean an eight character date like '20020102' would look like this in a SQL Server binary field 0x20020102
If this is true then you might try a modified version of my "Converting Binary data to a Hex Character String" that can be found here: http://qa.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=575
That modified script might look like this:
declare @binary_field varbinary(85)
declare @C int
declare @hexnum char(100)
set @binary_field = 0x20020102
set @hexnum = ''
while len(@binary_field) > 0
begin
set @C=cast(substring(@binary_field,1,1) as int)
set @binary_field=substring(@binary_field,2,len(@binary_field))
set @hexnum = rtrim(@hexnum) +
case
when (@c)/power(16,1)%16 = 0 then '0'
when (@c)/power(16,1)%16= 1 then '1'
when (@c)/power(16,1)%16= 2 then '2'
when (@c)/power(16,1)%16 = 3 then '3'
when (@c)/power(16,1)%16 = 4 then '4'
when (@c)/power(16,1)%16 = 5 then '5'
when (@c)/power(16,1)%16 = 6 then '6'
when (@c)/power(16,1)%16 = 7 then '7'
when (@c)/power(16,1)%16 = 8 then '8'
when (@c)/power(16,1)%16 = 9 then '9'
when (@c)/power(16,1)%16 = 10 then 'A'
when (@c)/power(16,1)%16 = 11 then 'D'
when (@c)/power(16,1)%16 = 12 then 'C'
when (@c)/power(16,1)%16 = 13 then 'D'
when (@c)/power(16,1)%16 = 14 then 'E'
when (@c)/power(16,1)%16 = 15 then 'F'
end +
case
when (@c)/power(16,0)%16 = 0 then '0'
when (@c)/power(16,0)%16= 1 then '1'
when (@c)/power(16,0)%16= 2 then '2'
when (@c)/power(16,0)%16 = 3 then '3'
when (@c)/power(16,0)%16 = 4 then '4'
when (@c)/power(16,0)%16 = 5 then '5'
when (@c)/power(16,0)%16 = 6 then '6'
when (@c)/power(16,0)%16 = 7 then '7'
when (@c)/power(16,0)%16 = 8 then '8'
when (@c)/power(16,0)%16 = 9 then '9'
when (@c)/power(16,0)%16 = 10 then 'A'
when (@c)/power(16,0)%16 = 11 then 'D'
when (@c)/power(16,0)%16 = 12 then 'C'
when (@c)/power(16,0)%16 = 13 then 'D'
when (@c)/power(16,0)%16 = 14 then 'E'
when (@c)/power(16,0)%16 = 15 then 'F'
end
end
PRINT CAST (@hexnum AS DATETIME)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 10:30 am
Thanks.... 🙂
September 7, 2007 at 12:31 pm
I believe the mainframe stores the date as a Julian date. If that is the case then here a link to a date converter.
http://aa.usno.navy.mil/data/docs/JulianDate.php
Here is also a link to a forum regarding Julian conversions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75091
~DH
September 7, 2007 at 4:13 pm
This is *very* helpful,
Thanks for sharing
* Noel
September 10, 2007 at 2:33 pm
EOJRR1, while a workaround for your packed decimal issue has been provided the 'root' cause still remains. It is generally accepted that when transferring data between disimilar operating systems (IBM to Windows) or disimilar hardware platforms (IBM to Wintel) that the transfer occurs in text format. Packed format is hardware specific. For example, way back in the day, Burroughs and IBM both supported packed decimal as most 'big iron' vendors did. However one used a the high order bit to denote the sign of the numeric value as opposed to the other which used a low order bit to denote the sign of the value. Even today, would you take the int value in native format from UDB2 and import it directly to the int value in SQL Server ? I think not. Well back to the root cause. I believe that the process on the mainframe side needs a slight bit of adjustment to convert the packed data item(s) to display data (text on the ascii side). Granted you may have inherited this 'as is' but that does not mean you cannot excercise 'due diligence' and improve the situation. Good Luck !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply