Getting ISO Date & Day of Week in BIRT

I was recently asked a very specific request from an end user about a date format in a report. The requirement was to have the the date in the report come out as ISO 860 date format and include the day of the week in parenthesis. So if the date on the report was Oct, 25, 2017, it should appear as 2017-10-25 (Wed). This is relatively easy to with the date formatting on just a date field and setting it to a custom date function of yyyy-MM-dd. 1 However, to get a mix of dates and days of the week, I knew this would be a dynamic text field. And the problem with dynamic text fields is you have to prep date formatting with the confines of javascript or BIRT variables.

Getting the ISO 8601 date format is pretty straight forward in a dynamic text field. You must take the date and break it down into 3 parts using the built in BIRT functions:

  • BirtDateTime.year( BirtDateTime.today() ) for the year
  • BirtDateTime.month( BirtDateTime.today() for the month
  • BirtDateTime.day( BirtDateTime.today() for the day

So I crated an if \ else statement to get the day of the week from the date. 2

if (BirtDateTime.weekDay( BirtDateTime.today() ) == 1) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Sun)'} if (BirtDateTime.weekDay( BirtDateTime.today() ) == 2) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Mon)'} if (BirtDateTime.weekDay( BirtDateTime.today() ) == 3) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Tue)'} if (BirtDateTime.weekDay( BirtDateTime.today() ) == 4) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Wed)'} if (BirtDateTime.weekDay( BirtDateTime.today() ) == 5) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Thu)'} if (BirtDateTime.weekDay( BirtDateTime.today() ) == 6) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Fri)'} if (BirtDateTime.weekDay( BirtDateTime.today() ) == 7) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Sat)'}

So the output of the of dynamic text for Oct 15, 2017 would be 2017-10-15 (Sun).

Perfect right?

Yeah, I thought it was too easy too. BIRT by default suppresses the leading zero for the BirtDateTime.day() function. So if the date was Nov 5, 2017, the output would actually be 2017-11-5 (Sun). That makes for an inconsistent presentation for ISO date format. The next trick was to use a child if \ else statement to check if the BirtDateTime.day() function was going to return a value between 1 and 9 or a value greater than 9.

if (BirtDateTime.weekDay( BirtDateTime.today() ) == 1) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Sun)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Sun)'} else if (BirtDateTime.weekDay( BirtDateTime.today() ) == 2) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Mon)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Mon)'} else if (BirtDateTime.weekDay( BirtDateTime.today() ) == 3) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Tue)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Tue)'} else if (BirtDateTime.weekDay( BirtDateTime.today() ) == 4) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Wed)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Wed)'} else if (BirtDateTime.weekDay( BirtDateTime.today() ) == 5) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Thu)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Thu)'} else if (BirtDateTime.weekDay( BirtDateTime.today() ) == 6) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Fri)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Fri)'} else if (BirtDateTime.weekDay( BirtDateTime.today() ) == 7) if (BirtDateTime.day( BirtDateTime.today() ) <= 9) { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '- 0' + BirtDateTime.day( BirtDateTime.today() ) + ' (Sat)' } else { BirtDateTime.year( BirtDateTime.today() ) + '-' + BirtDateTime.month( BirtDateTime.today() ) + '-' + BirtDateTime.day( BirtDateTime.today() ) + ' (Sat)'}

So now I have a canned function that will return an ISO 8601 date format with the day of the week.

Side note: In BIRT, the days of the week are as follows:

  • 1 = Sun
  • 2 = Mon
  • 3 = Tue
  • 4 = Wed
  • 5 = Thu
  • 6 = Fri
  • 7 = Sat

  1. Don't use mm because that's actually minutes in BIRT. ↩

  2. Note the use of BirtDateTime.today() makes the date selection independent of database type (SQL, ORA, DB2). ↩

  3. 150474 ↩

Comments

Top