For more information, please refer to this announcement explaining best practices for getting answers to questions.
How to Subtract 5 hours from current date in OTBI criteria
Answers
-
Hi Michael,
Please try the following:
TIMESTAMPADD(SQL_TSI_HOUR, -5, CURRENT_TIMESTAMP)
I'm attaching a report XML for your convenience.
Please let me know if it helps.
Kind Regards,
Bhaskar
1 -
Too funny, I JUST encountered this same error this morning.
I was trying to get people who were hired 30 days ago that have not created a goal in performance. When I did hire date < CURRENT DATE - 30, it worked. When I did hire date <= CURRENT_DATE - 30 days, it worked. When I did hire date = or in 30 days it bombed with the same error above. I also am putting an agent on so I needed exactly 30 days so people don't get harassed every day. My warped workaround that I found that works is to say:
Person Hire Date is between CURRENT_DATE - 30 and CURRENT_DATE - 30
Go figure!
Good luck!
0 -
Hi Michael,
Please find attached doc with a sample report for Effective Date & Custom Date Time.
Can you please execute the attached XML in your environment and share few records? We might need to format the column based on data type.
Just wanted to make sure that Effective Date is giving you the Date Time Values. As over here in our system it is not having Time Part.
Please give me a shout in case you need more information.
Kind Regards,
Bhaskar
0 -
Hi Michael,
I was trying to give you the code behind the - 5 hours calculation and I did with Current Timestamp as a result it will come same for all the rows.
Please find below the xml which will give you Effective Date - 5 hours. Could please let me know your requirement? I mean what data set you want to extract ?
<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sawx="com.siebel.analytics.web/expression/v1.1" xmlVersion="201201160">
<saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""Payroll - Payroll Balances Real Time"">
<saw:columns>
<saw:column xsi:type="saw:regularColumn" columnID="c1d99ce944eb678f1">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Balance Value Details"."Effective Date"</sawx:expr></saw:columnFormula>
<saw:displayFormat>
<saw:formatSpec suppress="suppress" wrapText="true">
<saw:dataFormat xsi:type="saw:custom" customFormat="DD/MM/YYYY HH:mm:ss"/></saw:formatSpec></saw:displayFormat>
<saw:columnHeading>
<saw:displayFormat>
<saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column>
<saw:column xsi:type="saw:regularColumn" columnID="c4d2e1105a188dd5f">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">TIMESTAMPADD(SQL_TSI_HOUR, -5, cast("Balance Value Details"."Effective Date" as timestamp))</sawx:expr></saw:columnFormula>
<saw:displayFormat>
<saw:formatSpec suppress="suppress" wrapText="true">
<saw:dataFormat xsi:type="saw:custom" customFormat="DD/MM/YYYY HH:mm:ss"/></saw:formatSpec></saw:displayFormat>
<saw:tableHeading>
<saw:caption fmt="text">
<saw:text>Balance Value Details</saw:text></saw:caption></saw:tableHeading>
<saw:columnHeading>
<saw:caption fmt="text">
<saw:text>Effective Date - 5 Hours</saw:text></saw:caption>
<saw:displayFormat>
<saw:formatSpec/></saw:displayFormat></saw:columnHeading></saw:column></saw:columns></saw:criteria>
<saw:views currentView="0">
<saw:view xsi:type="saw:compoundView" name="compoundView!1">
<saw:cvTable>
<saw:cvRow>
<saw:cvCell viewName="titleView!1"/></saw:cvRow>
<saw:cvRow>
<saw:cvCell viewName="tableView!1"/></saw:cvRow></saw:cvTable></saw:view>
<saw:view xsi:type="saw:titleView" name="titleView!1"/>
<saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="true">
<saw:edges>
<saw:edge axis="page" showColumnHeader="true"/>
<saw:edge axis="section"/>
<saw:edge axis="row" showColumnHeader="true">
<saw:edgeLayers>
<saw:edgeLayer type="column" columnID="c1d99ce944eb678f1"/>
<saw:edgeLayer type="column" columnID="c4d2e1105a188dd5f"/></saw:edgeLayers></saw:edge>
<saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>Kind Regards,
Bhaskar
0