Sql How To Get Comp-3 Packed Decimal?

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

Solution 1:

If you store an integer date (ie 20120123) as a character string the hex representation would be 0x3230313230313233 where 32 = 2, 30 = 0 etc, which is 8 bytes (ie 32 30 31 32 30 31 32 33) of storage.

In a packed decimal format the representation of the same string would be: 0x020120123F The F is a bit to show that this is an unsigned integer. The other numbers are stored as half of a byte for each digit. So you can see that a common date string would fit into a 5 byte (ie 02 01 20 12 3F ) field.

So, to use this in SSIS you would probably have to do as @billinkc stated above and use a Script Transformation to transform the field. The mechanics of this would be to count the digits in your number, pad with 0's on the left to get your characters up to 9 for comp-3 5 and 7 for comp-3 4 then construct a hexidecimal string with the digits from your date or time and add a F at the end (or a C if your destination expects a signed number).