cognos - 前缀 DateTime 变量

Question

我目前是 cognos 10.2 报表工作室的新手。我需要在我的 sql 中声明前缀日期时间，以使我的联合查询工作。我已经测试了一些日期时间声明，但它似乎不起作用，并且我不断让服务器返回无法识别的查询框架响应。

我已经尝试了一些我在一些 cognos 论坛中找到的代码，如下所示。

Codes that i've tried
1. '1970-01-01T00:00:00.000000000' as invdate
2.  todate(null) as invdate

---

/********** This below is my code ***********/
select 
'fstgld' as wso,
0 as pono,
'nosh' as shpm,
'gld' as DocType,
0 as DocNo,
'gl' as item,
trim(tffst305.dim2) as ItemGroup,
tffst305.year as fy,
tffst305.perd as period,
'fst' as slsordtype,
'finbg' as finbg,
0 as Qty,
tffst305.leac as leac,
0 as Sales,
tffst305.fdah-tffst305.fcah as Cost
    current_date as invdate      <------this is the part where i keep getting error as i need to declare a prefix datetime

 From tffst305
 WHERE 
 tffst305.ptyp = 1 and
 tffst305.budg ='ACT' and
 tffst305.company_nr = 810 

 union all

 select 
cisli310.orno as wso,
cisli310.pono as pono,
cisli310.shpm as shpm,
cisli310.tran as DocType,
cisli310.idoc as DocNo,
cisli310.item as item,
tdsls411.citg as ItemGroup,
tfgld018.year as fy,
tfgld018.vprd as period,
cisli310.sotp as slsordtype,
tccom112.cfcg as finbg,
cisli310.dqua as Qty,
'inv' as leac,
cisli310.amth(1) as Sales,
0 as Cost,
    cisli305.idat as invdate  <--- extracted from the table field

 From cisli310
 RIGHT OUTER JOIN cisli305 ON cisli310.tran = cisli305.tran and 
 cisli310.idoc = cisli305.idoc
 LEFT OUTER JOIN tdsls411 ON cisli310.orno=tdsls411.orno and 
 cisli310.pono=tdsls411.pono
 LEFT OUTER JOIN tccom112 ON cisli305.ofbp = tccom112.itbp
 inner join tfgld018 on cisli310.tran = tfgld018.ttyp and cisli310.idoc = 
 tfgld018.docn 
 WHERE 

 cisli310.sotp  in ('SSP', 'SPL', 'SWK') and cisli310.amth(1) <>0 and
 cisli305.company_nr=810 and 
 cisli310.company_nr=810 and
 tdsls411.company_nr=810 and
 tfgld018.company_nr=810 and
 tccom112.company_nr=810 

记录的字段是日期时间数据类型，例如 2009-07-03 03:08:03pm

标签： cognos

Answer 1

尝试将 current_date 替换为 #timestampMask ( $current_timestamp , 'yyyy-dd-mm' ) # 您可以根据需要添加其他日期或时间部分

timestampMask ( string_expression1 , string_expression2 ) 返回“string_expression1”，表示带有时区的时间戳，修剪为“string_expression2”中指定的格式。“string_expression2”中的格式必须是以下之一：'yyyy'、'mm'、'dd'、'yyyy-mm'、'yyyymm'、'yyyy-mm-dd'、'yyyymmdd'、'yyyy- mm-dd hh:mm:ss', 'yyyy-mm-dd hh:mm:ss+hh:mm', 'yyyy-mm-dd hh:mm:ss.ff3', 'yyyy-mm-dd hh: mm:ss.ff3+hh:mm'、'yyyy-mm-ddThh:mm:ss'、'yyyy-mm-ddThh:mm:ss+hh:mm'、'yyyy-mm-ddThh:mm:ss。 ff3+hh:mm'，或'yyyy-mm-ddThh:mm:ss.ff3+hh:mm'。返回带有时区的时间戳的字符串表示的宏函数默认显示秒的小数部分的 9 位精度。格式选项允许将其缩减到 3 或 0 的精度。