It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×359
×182
×112
×85

question asked: Jul 29 '16, 6:27 a.m.
question was seen: 4,089 times
last updated: Sep 02, 7:29 a.m.

Related questions

How to convert CURRENT_DATE into DateTime format in JRS

0
Shubhangi yadav (13●2●8) | asked Jul 29 '16, 6:27 a.m.
Hi Team,

I have a scenario where I need to count days between CREATION_DATE & CURRENT_DATE in advance query of JRS, but due difference in date format I am unable to count the days between CREATION_DATE & CURRENT_DATE.

To count days I have used statement -> round(CURRENT_DATE - T1.CHANGE_DATE) AS DAYS_IN_CURRENT_STATE

Example:  CREATION_DATE is in '3/2/14 9:09 AM' format
                and 
                CURRENT_DATE is in '2016-07-29T12:25:35' format

this is very urgent, can someone please provide solution for this.

Thanks!!

2 answers

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
Greg Pflaum (67●9●13) | answered Jul 29 '16, 5:31 p.m.
Try this:
       DAYS(CURRENT DATE) - DAYS(T1.CHANGE_DATE) AS DAYS_IN_CURRENT_STATE
Comments
Shubhangi yadav commented Aug 01 '16, 12:30 a.m.

Hi Greg, thank you for your answer but I and getting error while using DAYS in JRS advance query.

CRRGW5628E An java.sql.SQLSyntaxErrorException error occurred when validating the input SQL string, caused by ORA-00904: "DAYS": invalid identifier . 9:57:37 AM

An unexpected error occurred when validating the input SQL string.

Check the reporting service log file for details.




1
Steven Shaw commented Aug 02 '16, 10:11 a.m.
FORUM MODERATOR / JAZZ DEVELOPER

 Which Data Warehouse vendor do you use (DB2 vs Oracle vs SQL Server).  Depending on the vendor the syntax may be different.


One tip is to generate a report that has a condition similar to what you want in your advanced query and then copied the generated select statement into the SQL section.  For instance - what you're suggesting sounds similar to adding a condition on "Creation Date" with a relative date condition (i.e. > 20 days ago) etc.

-Steve

Chris Ball commented Jul 09 '19, 9:19 a.m.

That is a very useful tip! Thanks. 
0
permanent link
Varad Joshi (11) | answered Sep 02, 7:29 a.m.
If you are using Oracle vendor , Try This.  
TRUNC(CURRENT DATE) - TRUNC(T1.CHANGE_DATE) AS DAYS_IN_CURRENT_STATE

Your answer

Register or to post your answer.


Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.