DB2 中将date类型的转换成timestamp

方法1：

TIMESTAMP(Char(date)||'00.00.00')

 

SELECT T2.RECORDNO,T2.DANGERTIME,T2.BIZORIGIN,T3.COMMONNAME AS BIZORIGINNAME
 ,T2.ADDRESS,'' AS REPORTNO,'' AS REPORTDATE,T1.INSURERCODE,T4.COMMONNAME AS INSURECOMPANYNAME
 ,T1.COMMERCIALNO,'1' AS CUSTOMERNO,T2.FRAMENO,T2.LICENSENO AS CARLICENSENO
 ,T8.MODELCODE,T9.REPAIRNO
 FROM  RT_DANGERRECORD T2 
 LEFT JOIN RT_INSURANCEBUY T1 ON T2.FRAMENO = T1.FRAMENO AND T2.DANGERTIME > TIMESTAMP(CHAR(T1.INSURANCEBUYDATE)||'00.00.00') AND T2.DANGERTIME <TIMESTAMP(CHAR(T1.INSURANCEMATURITYDATE)||'00.00.00')
 LEFT JOIN CM_COMMONCODE T3 ON T2.BIZORIGIN = T3.COMMONCODE AND T3.CODETYPE = 'CR25'
 LEFT JOIN CM_COMMONCODE T4 ON T1.INSURERCODE = T4.COMMONCODE AND T4.CODETYPE = 'CC12'

 LEFT JOIN VM_CARINFO T6 ON T1.FRAMENO = T6.FRAMENO
 LEFT JOIN VM_MODELCODE T7 ON T6.MODELTYPEID = T7.MODELTYPEID
 LEFT JOIN VM_MODEL T8 ON T7.MODELCODEID = T8.MODELCODEID
 LEFT JOIN RT_REPAIR T9 ON T2.FRAMENO = T9.FRAMENO

 

方法2：

TIMESTAMP(CHAR(date),'00:00:00')

示例：

SELECT T2.RECORDNO,T2.DANGERTIME,T2.BIZORIGIN,T3.COMMONNAME AS BIZORIGINNAME ,T2.ADDRESS,'' AS REPORTNO,
 '' AS REPORTDATE,T1.INSURERCODE,T4.COMMONNAME AS INSURECOMPANYNAME ,T1.COMMERCIALNO AS INSURANCENO,
 T1.INSURED,T2.FRAMENO,T2.LICENSENO AS CARLICENSENO ,
 T8.MODELCODE,T9.REPAIRNO FROM  
 RT_DANGERRECORD T2  
 LEFT JOIN RT_INSURANCEBUY T1 ON T2.FRAMENO = T1.FRAMENO 
 AND T2.DANGERTIME > TIMESTAMP(CHAR(T1.INSURANCEBUYDATE),'00:00:00') AND T2.DANGERTIME < TIMESTAMP(CHAR(T1.INSURANCEMATURITYDATE), '00:00:00') 
 LEFT JOIN CM_COMMONCODE T3 ON T2.BIZORIGIN = T3.COMMONCODE AND T3.CODETYPE = 'CR25' 
 LEFT JOIN CM_COMMONCODE T4 ON T1.INSURERCODE = T4.COMMONCODE AND T4.CODETYPE = 'CC12' 
 LEFT JOIN VM_CARINFO T6 ON T2.FRAMENO = T6.FRAMENO LEFT JOIN VM_MODELCODE T7 ON T6.MODELTYPEID = T7.MODELTYPEID LEFT JOIN VM_MODEL T8 ON T7.MODELCODEID = T8.MODELCODEID 
 LEFT JOIN RT_REPAIR T9 ON T2.FRAMENO = T9.FRAMENO AND T9.REPAIRNO = 'H201308300001'
  WHERE T2.FRAMENO = 'LHGGM2633C2023206'