It's all about the answers!

Ask a question

How to convert CURRENT_DATE into DateTime format in JRS


Shubhangi yadav (1328) | 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!!

One answer



permanent link
Greg Pflaum (67913) | 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. 

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.