如何在Teradata中添加日期？ [英] how to add date in Teradata?

问题描述

我想添加01/01/1970到一列，last_hit_time_gmt的数据类型是bigint，当我运行下面的查询我得到数据类型

last_hit_gmmt

与定义的数据类型名称不匹配。

  select 
 distinct STG.OMN_APND_KEY，
 STG.last_hit_time_gmt，
 IIF（STG.last_hit_time_gmt 0，ADD_TO_DATE（TO_DATE（' 01/01/1970'，'DD / MM / YYYY'），'SS'，转换（STG.last_hit_time_gmt为DATE），NULL）
来自EDW_STAGE_CDM_SRC.STG_OMNITUREDATA STG 
 WHERE 
 UPPER （STG_OMNITUREDATA.EVAR41）IN 
（'CONS_SUPP：CONSUMER'，'STORE'，'PURCHASE'）和
 STG.OMN_APND_KEY ='61855975'
  pre> 
 
 
请帮助我.. 
解决方案
查询和数据类型与Teradata不兼容。
 
 
 
 
 
如意见中所述，您可能希望使用CASE而不是IFF ral格式是
 
 
 
 
 
 
 
 CASE WHEN *条件* THEN * result_if_true * 
 ELSE * result_if_false * 
 END as * ColumnName * 
 
 
 根据评论回复编辑 
 
 
 
所以在你的查询示例中，case语句可以像...一样使用
 
 
选择不同的STG.OMN_APND_KEY 
，STG.last_hit_time_gmt 
，CASE WHEN STG.last_hit_time_gmt = 0 THEN NULL 
 ELSE DATE'1970-01-01'
 END AS YourColName 
 FROM EDW_STAGE_CDM_SRC.STG_OMNITUREDATA STG 
 WHERE UPPER（STG_OMNITUREDATA.EVAR41）IN 
（'CONS_SUPP：CONSUMER'，'STORE'，'PURCHASE'）和
 STG .OMN_APND_KEY ='61855975'
 
 
此外，如果您只是想更新STG.last_hit_time_gmt字段，为什么不只需使用两个简单的 UPDATE 语句？
 
 
更新EDW_STAGE_CDM_SRC.S TG_OMNITUREDATA 
 SET STG.last_hit_time_gmt = DATE'1970-01-01'
 WHERE STG.last_hit_time_gmt<> 0 
 AND UPPER（STG_OMNITUREDATA.EVAR41）IN 
（'CONS_SUPP：CONSUMER'，'STORE'，'PURCHASE'）
 AND STG.OMN_APND_KEY ='61855975'; 
 
更新EDW_STAGE_CDM_SRC.STG_OMNITUREDATA 
 SET STG.last_hit_time_gmt = NULL 
 WHERE STG.last_hit_time_gmt = 0 
和UPPER（STG_OMNITUREDATA.EVAR41）IN 
（' CONS_SUPP：CONSUMER'，'STORE'，'PURCHASE'）
 AND STG.OMN_APND_KEY ='61855975'; 
 
 
Hi i want to add 01/01/1970 to a column ,datatype of last_hit_time_gmt is bigint ,when i run the below query i am getting data type 

  
last_hit_gmmt
does not match a defined datatype name.
   select 
    distinct STG.OMN_APND_KEY,
    STG.last_hit_time_gmt,
    IIF(STG.last_hit_time_gmt  <>0,ADD_TO_DATE(TO_DATE('01/01/1970', 'DD/MM/YYYY'),'SS',cast(STG.last_hit_time_gmt as DATE ),NULL) 
from    EDW_STAGE_CDM_SRC.STG_OMNITUREDATA STG
 WHERE
 UPPER(STG_OMNITUREDATA.EVAR41) IN 
('CONS_SUPP: CONSUMER','STORE','PURCHASE')  and 
STG.OMN_APND_KEY='61855975'
please help me..
解决方案
The query and data type is incompatible with Teradata.


As stated in the comments you may want to use "CASE" instead of "IFF". The general format is 



CASE WHEN *condition* THEN *result_if_true*
 ELSE *result_if_false*
END as *ColumnName*

editing based on comment response

So in your query example the case statement can be used like... 

    select distinct STG.OMN_APND_KEY
     ,STG.last_hit_time_gmt
     ,CASE WHEN STG.last_hit_time_gmt = 0 THEN NULL 
       ELSE DATE '1970-01-01'
      END AS YourColName
    FROM EDW_STAGE_CDM_SRC.STG_OMNITUREDATA STG
    WHERE UPPER(STG_OMNITUREDATA.EVAR41) IN 
    ('CONS_SUPP: CONSUMER','STORE','PURCHASE')  and 
    STG.OMN_APND_KEY='61855975'

Also, if you are merely just trying to update the field STG.last_hit_time_gmt, why not just use two simple UPDATE statements?

    UPDATE EDW_STAGE_CDM_SRC.STG_OMNITUREDATA 
     SET STG.last_hit_time_gmt = DATE '1970-01-01'
    WHERE STG.last_hit_time_gmt <>  0
     AND UPPER(STG_OMNITUREDATA.EVAR41) IN 
        ('CONS_SUPP: CONSUMER','STORE','PURCHASE')  
     AND STG.OMN_APND_KEY='61855975';

    UPDATE EDW_STAGE_CDM_SRC.STG_OMNITUREDATA 
     SET STG.last_hit_time_gmt = NULL
    WHERE STG.last_hit_time_gmt =  0
     AND UPPER(STG_OMNITUREDATA.EVAR41) IN 
        ('CONS_SUPP: CONSUMER','STORE','PURCHASE')  
     AND STG.OMN_APND_KEY='61855975';


                        
这篇关于如何在Teradata中添加日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！