Skip Navigation
Expand
Displaying date and time in a report
Answer ID 2594   |   Last Review Date 11/14/2018

How do I change the display of Date/Time in an output column?

Environment:

Analytics, Format Tokens for Date and Time

Resolution:

To format the display of a column that displays a date/time:

  1. Open the report in Edit mode.
  2. Right-click on the column whose formatting you wish to change.
  3. Choose "Edit Format"
  4. Click on the "Date" tab if it is not already selected.
  5. Pull down the "Date format" menu and select the desired format. You can also customize your own formats.
  6. Click “OK” and save the report.

To Customize Date format:

To display the format “Month / Day / Year  Hours: Minutes: Seconds AM/PM,” choose "Custom" from the date Format menu and enter "%m/%d/%Y %I:%M:%S %p" for the format string.

For a list of Date and Time Format Tokens you can use, search on "Date and time format tokens for analytics" within the Documentation for Oracle RightNow Products. To access Oracle Service Cloud manuals and documentation online, refer to the Documentation for Oracle Service Cloud Products..


Directly change the output using the date_format function:

  1. Edit the report.
  2. Right-click on the column whose formatting you wish to change.
  3. Choose "Edit Definition".
  4. Expand the “Functions” section of the “Available Columns/Functions” list. Expand the “Date Functions” section. Double click on “date_format(date, format)” to copy it over to the “Expression” box.
  5. Replace the “date” parameter with the field you wish to format.
  6. Replace the format with the desired format. (See online documentation for 'Date and Time Format tokens', link above)
  7. Click “OK” and save the report.


For example, to display the date/time an incident was created in the format “Weekday, Month Day” you could enter “date_format(incidents.created, 'DAY, MONTH D')” for the column definition.


Allow users to choose the Date/Time formatting when they run the report by using a variable:

  1. Open the report in edit mode.
  2. From the Home tab, click Variables located in the Data Set section to Add a variable. 
  3. Enter an appropriate name for the variable, such as “$Date”.
  4. Enter an appropriate prompt for the variable, such as “Choose Date Formatting”.
  5. Choose Type “Predefined List.”.
  6. Choose List Name “Group Date”.
  7. Choose a default value.
  8. Right-click on the column whose formatting you wish to change.
  9. Choose "Edit Definition".
  10. Expand the “Functions” section of the “Available Columns/Functions” list. Expand the “Date Functions” section. Double click on “date_format(date, format)” to copy it over to the “Expression” box.
  11. Replace the “date” parameter with the field you wish to format.
  12. Replace the format with your variable.
  13. Click “OK” and save the report.


For example, to allow the user to choose the date time formatting for the incident creation date/time when running the report, use a column definition of  “date_format(incidents.created, $Date)” assuming your variable is named “$Date.” Now, when users run the report, they will be prompted to choose a date formatting.