SQL How to get COMP-3 Packed Decimal?

Question

I am creating an ASCII output file containing some information, and two of my fields require the date and time to be in packed decimal fields (COMP-3). I use SQL and SSIS to generate my file.

Field Name Format Length Picture Date YYYYMMDD 5 S9(8) C-3 Time HHMMSS 4 S9(6) C-3 

I searched several sites and I still can’t figure out how to convert the date / time to a packed decimal field. Any help would be greatly appreciated.

For information on packed fields, the following http://www.simotime.com/datapk01.htm#TablePackedDecimal was provided. I understand how packed fields are used, but I don’t really understand how to pack a field. Thanks!

Answer 1

If you store the whole date (i.e. 20120123) as a character string, the hexadecimal representation will be 0x3230313230313233, where 32 = 2, 30 = 0, etc., which is 8 bytes (i.e. 32 30 31 32 30 31 32 33) memory

In packed decimal format, the representation of the same line will be: 0x020120123F F is a bit to show that it is an unsigned integer. The remaining numbers are stored as half bytes for each digit. Thus, you can see that the general date string is inserted into the 5 byte field (i.e. 02 01 20 12 3F).

So, to use this in SSIS, you probably have to do it like @billinkc above and use the Script transformation to convert the field. The mechanics of this will be to count the numbers in your number, pad from 0 to the left to get your characters to 9 for comp-3 5 and 7 for comp-3 4, and then build a hexadecimal string with numbers from your date or time and add F at the end (or C if your destination is expecting a signed number).