This article provides information on how to work with the Date column type from the macro editor.

Instructions

1. In the relevant Confluence page, add or update the required SQL Query macro. 
2. Specify the required Data source profile, enter the SQL query in SQL statement, and click Run to view the retrieved data if the macro editor is opened for the first time, or proceed to the next step.
3. Click the down arrow above the relevant column containing dates and click Column type > Date.

The succeeding sections address the following scenarios if the Date column type is selected:

1. If the Input input format is a string
2. If the Input input format is not in a standard International Organization for Standardization (ISO) format compliant

Anchor
sqlCld_120_tut_handleDates_isString sqlCld_120_tut_handleDates_isString

Input format is a string

1. Leave the Input format unchanged.
2. Provide the required format tokens to change the display format of the dates, or leave this parameter unchanged.
3. Click Apply to change the display format.

...

The app automatically detects the input format that is ISO standard compliant and is supported by the Moment.js library. Refer to this article to know more about the supported formatting options and this article to know more about format tokens.

Anchor
sqlCld_120_tut_handleDates_notISO sqlCld_120_tut_handleDates_notISO

Input format is not in the standard ISO format

In this case, the app does not recognize the date format and may show the original data or a blank value for such dates. Here, you must specify the input format so that the app can understand the given date/time tokens and format the data as desired.

Let's consider the following example where the Date of Joining column does not follow standard date format:

Image Added

Specify Input format as dd MM yyyy and Display format as yyyy-DD-MMM.

Image Added

The output is then as follows:

Image Added

Additional references

• Key concepts
• SQL Query macro