SQL如何获取COMP-3压缩十进制? [英] SQL How to get COMP-3 Packed Decimal?

问题描述

我正在创建一个包含一些信息的ASCII输出文件，并且我的两个字段要求日期和时间位于压缩的十进制字段(COMP-3)中.我正在使用SQL和SSIS生成文件.

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 generate my file.

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

我搜索了多个站点，但仍然不知道如何将日期/时间转换为压缩的十进制字段.任何帮助将不胜感激.

I have searched multiple sites and I still can't figure out how to convert a date/time into a packed decimal field. Any help would be greatly appreciated.

以下站点 http://www.simotime.com/datapk01.htm#TablePackedDecimal.我了解打包字段的使用方式，但并不真正了解如何打包字段.谢谢！

The following site http://www.simotime.com/datapk01.htm#TablePackedDecimal was provided for information about Packed fields. I understand how packed fields are used, but don't really understand how to pack a field. Thanks!

推荐答案

如果您将整数日期(例如20120123)存储为字符串，则十六进制表示形式为0x3230313230313233，其中32 = 2、30 = 0等，即8字节(即32 30 31 32 30 31 32 33)的存储空间.

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.

以压缩十进制格式表示的相同字符串为: 0x020120123F F用来表明这是一个无符号整数.其他数字存储为每个数字的一​​半字节.因此，您可以看到一个公共日期字符串将适合5字节(即02 01 20 12 3F)字段.

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.

因此，要在SSIS中使用此功能，您可能必须执行上述@billinkc的操作，并使用脚本转换来转换该字段.其机制是计算您数字中的数字，在左边用0填充，以使comp-3 5的字符最多为9，comp-3 4的字符最多为7，然后构造一个十六进制的字符串，其中包含日期中的数字或时间，并在末尾添加F(如果目的地需要带符号的数字，则添加C).

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

这篇关于SQL如何获取COMP-3压缩十进制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！