HowTo:Compute a Date Value in a View or Report

For:   Designers Level: Intermediate Time:  10 minutes See more:     ◾ HowTo Guides

This guide shows how to compute a value for a view or report, without creating a data field in the record, and how to convert a date so the month appears as text.

Convert a Date to Text

<syntaxhighlight lang="java" enclose="div">

CONCAT( IF( MONTH(DATECREATED)=01, 'Jan, ', IF (MONTH(DATECREATED)=02, 'Feb, ', IF (MONTH(DATECREATED)=03, 'Mar, ', IF (MONTH(DATECREATED)=03, 'Apr, ', IF (MONTH(DATECREATED)=03, 'May, ', IF (MONTH(DATECREATED)=03, 'Jun, ', IF (MONTH(DATECREATED)=03, 'Jul, ', IF (MONTH(DATECREATED)=03, 'Aug, ', IF (MONTH(DATECREATED)=03, 'Sep, ', IF (MONTH(DATECREATED)=03, 'Oct, ', IF (MONTH(DATECREATED)=11, 'Nov, ', 'Dec, ') ) ) ) ) ) ) ) ) ) ) ,

  TEXT(YEAR(DATECREATED) ) )

</syntaxhighlight>

Other Options

Here are some other ways to achieve the same goal:

1. Create a Formula Field in the record that tests the month number to store the appropriate string for the month (IF(MONTH(DATECREATED)=01, 'January ', etc.). Then add a computed field to the View that tacks on TEXT(YEAR(DATECREATED)) to that value.

2. Create a text field in the record for the month, then create a Data Policy that runs on add and update. Execute Java code in the data policy, and put the month names in a HashMap or Array. Then tack on the year value, as above.

   Create a Utility class. In that class, create a function that takes a date argument and returns a string in the desired format. Use an add/update data policy to do the conversion. In the data policy, access the function to store the month/day form in the record. (Advantage: The function can be used for any date. Disadvantage: Extra storage space for the same value, in two formats.)

3. Use a JSP page and a SQL query to create the desired view, and then add Javascript functions to extract the part of the date you want to display. (Advantage: No extra space required. Disadvantage: More hacking required--unless you enjoy hacking...)

   Learn more: HowTo:Use_a_SQL_Query_to_List_Records_in_a_Custom_Page