SAP HANA SQL中FLOAT类型存储的日期如何转换为标准DATE格式

存储逻辑说明 #

你遇到的浮点型日期存储是SAP分类属性字段的常见存储逻辑：ATFLV存储的2.0181218000000000E+07本质是YYYYMMDD格式的日期直接转成的数值，转换为整数后为20181218，对应日期为2018年12月18日。该值不属于时间戳类型，无需使用1900或1970基准日期做偏移计算，此前的时间戳转换思路不符合该字段的存储规则。

基础转换语句 #

HANA SQL中可直接通过类型转换+日期格式化实现转换，样例代码如下：

-- 基础转换，测试样例数据可直接返回2018-12-18
SELECT 
  TO_DATE(TO_VARCHAR(CAST(ATFLV AS INTEGER)), 'YYYYMMDD') AS CONVERTED_DATE
FROM AUSP
WHERE OBJEK = '000000000000004148' AND ATINN = '9999999400';

如果存在非法存储值需要避免转换报错，可使用容错函数：

-- 非法值兼容：转换失败返回NULL，不打断查询
SELECT 
  TRY_TO_DATE(TO_VARCHAR(CAST(ATFLV AS INTEGER)), 'YYYYMMDD') AS CONVERTED_DATE
FROM AUSP;

可用于计算视图的表函数示例 #

以下是可直接在HANA Studio中创建的表函数模板，可根据业务需求调整返回字段和过滤条件：

CREATE FUNCTION "你的SCHEMA名称"."F_AUSP_ATFLV_TO_DATE"()
RETURNS TABLE (
  MANDT NVARCHAR(3),
  OBJEK NVARCHAR(50),
  ATINN NVARCHAR(10),
  KLART NVARCHAR(3),
  ATFLV FLOAT,
  CONVERTED_DATE DATE
)
LANGUAGE SQLSCRIPT
AS
BEGIN
  RETURN SELECT
    MANDT,
    OBJEK,
    ATINN,
    KLART,
    ATFLV,
    TRY_TO_DATE(TO_VARCHAR(CAST(ROUND(ATFLV,0) AS INTEGER)), 'YYYYMMDD') AS CONVERTED_DATE
  FROM AUSP;
END;

注意事项 #

• 如果ATFLV字段存在小数位存储，提前用ROUND(ATFLV, 0)取整可避免浮点精度损失导致的转换错误
• 若确认该分类属性存储的是带时分秒的时间戳（数值为14位，量级为1E+13），可将格式化字符串调整为'YYYYMMDDHH24MISS'，使用TO_TIMESTAMP函数转换即可

内容的提问来源于stack exchange，提问作者kakashi1208