One of the challenges recently thrown my way was to read an EBCDIC flat file, decode packed dates, and insert the dates into a SQL table. For those unfamiliar with packed data, it is a way to store data at the nibble level (half a byte), and was often used by mainframe programmers to conserve storage space. In the case of my input file, the dates were 2 bytes long and represented the number of days that have past since 01/01/1950. My first thought was, in the words of Scooby, Hmmmmph? But, I love a good challenge, so I dove in.
![Read Read](/uploads/1/2/5/8/125866848/849734057.png)
Reading in the flat file was rather simple. The only difference between reading an EBCDIC and an ASCII file is the Code Page option in the connection manager. In my case, I needed to use Code Page 1140 for EBCDIC (I could have also used Code Page 37).
-- -- Author: Jim Giercyk -- Create date: March, 2012 -- Description: Converts a Hex string to a decimal value -- CREATE FUNCTION [dbo].[ftn_HexToDec] ( @hexValue NVARCHAR(6) ) RETURNS DECIMAL AS BEGIN -- Declare the return variable here DECLARE @decValue DECIMAL IF @hexValue LIKE '0x%' SET @hexValue = SUBSTRING(@hexValue,3,4) DECLARE @decTab TABLE ( decPos1 VARCHAR(2), decPos2 VARCHAR(2), decPos3 VARCHAR(2), decPos4 VARCHAR(2) ) DECLARE @pos1 VARCHAR(1) = SUBSTRING(@hexValue,1,1) DECLARE @pos2 VARCHAR(1) = SUBSTRING(@hexValue,2,1) DECLARE @pos3 VARCHAR(1) = SUBSTRING(@hexValue,3,1) DECLARE @pos4 VARCHAR(1) = SUBSTRING(@hexValue,4,1) INSERT @decTab VALUES (CASE WHEN @pos1 = 'A' THEN '10' WHEN @pos1 = 'B' THEN '11' WHEN @pos1 = 'C' THEN '12' WHEN @pos1 = 'D' THEN '13' WHEN @pos1 = 'E' THEN '14' WHEN @pos1 = 'F' THEN '15' ELSE @pos1 END, CASE WHEN @pos2 = 'A' THEN '10' WHEN @pos2 = 'B' THEN '11' WHEN @pos2 = 'C' THEN '12' WHEN @pos2 = 'D' THEN '13' WHEN @pos2 = 'E' THEN '14' WHEN @pos2 = 'F' THEN '15' ELSE @pos2 END, CASE WHEN @pos3 = 'A' THEN '10' WHEN @pos3 = 'B' THEN '11' WHEN @pos3 = 'C' THEN '12' WHEN @pos3 = 'D' THEN '13' WHEN @pos3 = 'E' THEN '14' WHEN @pos3 = 'F' THEN '15' ELSE @pos3 END, CASE WHEN @pos4 = 'A' THEN '10' WHEN @pos4 = 'B' THEN '11' WHEN @pos4 = 'C' THEN '12' WHEN @pos4 = 'D' THEN '13' WHEN @pos4 = 'E' THEN '14' WHEN @pos4 = 'F' THEN '15' ELSE @pos4 END) SET @decValue = (CONVERT(INT,(SELECT decPos4 FROM @decTab))) + (CONVERT(INT,(SELECT decPos3 FROM @decTab))*16) + (CONVERT(INT,(SELECT decPos2 FROM @decTab))*(16*16)) + (CONVERT(INT,(SELECT decPos1 FROM @decTab))*(16*16*16)) RETURN @decValue END GO |
SELECT[packedDate] AS 'Hex Value', dbo.ftn_HexToDec([packedDate]) AS 'Decimal Value', CONVERT(DATE,DATEADD(day,dbo.ftn_HexToDec([packedDate]),'01/01/1950'),101) AS 'Relative String Date' FROM [dbo].[Output Table] |