Working with Dates in JSS

Convert Date in String field to a Real Date Field

When using the BI Tool with custom reports, working with dates can be tricky because all your fields are imported as text fields (aka java.lang.String). Unlike numeric values, you cannot simply change the class of the field. So you have to do a bit of work to transform a date in a String field to a real date value so that you can use the ever useful date calculation functions. It can be tricky to figure it out, but once you know what to do, it doesn't take more than a minute. This should help!

First, you'll need to pull out the year, month, and day of the date using the .substring command.

Then, you have to use the INTEGER_VALUE() command to turn the substring into an integer.

Finally, you use the DATE() command to turn the integers into a real date value.

Set the class of your variable to:

    java.util.Date

This will allow you to do date calculations with your date. All this can be done with a single variable.

See screenshot:

You can copy and paste this formula into your variable expression:

$F{Insert Field Name Here}.equals("")
    ? null
    : DATE(
        INTEGER_VALUE($F{Insert Field Name Here}.substring(6)),
        INTEGER_VALUE($F{Insert Field Name Here}.substring(0,2)),
        INTEGER_VALUE($F{Insert Field Name Here}.substring(3,5))
    )

Here is a link that explains how to use the substring function.

Now that you have a real date field. You can use functions like YEARS(Date1,Date2) or DAYS(Date1,Date2) to calculate the number of years or days between two days.

HINT: You can use the TODAY() function to dynamically get today's date. That allows you to create a variable that will dynamically calculate things like: Number of Dates/Years Since EL Reclassification Date.