How to convert CURRENT_DATE into DateTime format in JRS
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!!
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
Try this:
DAYS(CURRENT DATE) - DAYS(T1.CHANGE_DATE) AS DAYS_IN_CURRENT_STATE
DAYS(CURRENT DATE) - DAYS(T1.CHANGE_DATE) AS DAYS_IN_CURRENT_STATE
Comments
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.
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
1 vote
That is a very useful tip! Thanks.