FORMAT

(null Datenull dateFormat)
Returns date in specified format.
Example: FORMAT(DATE(2008, 3, 9, 16, 5, 7), "MM/dd/yyyy") returns 03/09/2008.
Date: Required. The date.

Example 1

A date column usually comes in the US English format - as seen in the first column. To present the date in the "dd-MM-yyyy" format:

format(&Date,"dd-MM-yyyy")

Example 2

Full Month Name from date:

format(&Date,"MMMM")

Pattern options:

Example 3

Extensively formatted date:

format(&Date,"ddd dd MMM yyyy")

 

Was this article helpful?
0 out of 0 found this helpful

Comments

2 comments
  • Hi Ole,

    If I have a dashboard or report showing the date format as this (dd-mm-yyyy): 

    Date Format comes like this from our ERP/DWH.

    Is it in anyway possible to change the date format to be shown as either dd.mm.yyyy or yyyymmdd  ?

    How / where do I change the format if possible ?

    It's for a scheduled report that is going to be sent to a partner outside the organisation which uses the the alternative date formats when importing the report into their systems. 

    0
  • Hi Morten,

    There is no immediate way you can change dimension members formatting in the client, so this is something that will usually need to be done in the DWH.

    Potentially, you already have a key in the yyyymmdd format for this dimension in your DWH - and it just needs to made available to you, e.g., as a DateNumber attribute.

    A long shot alternative is to use Data Discovery to produce the correct format. If you should want to go down this path, here are the steps you will need to take:

    1. Create a dashboard with the table as shown in your screen shot.
    2. Save the dashboard.
    3. Open Data Discovery and create a 'TARGIT Analysis' data source pointing to the dashboard/table.
    4. Right click and add a Format to that data source.
    5. Using the FORMAT function (this article), create a new column with the desired date format.
    6. Create a Data Discovery cube from the data source Format.
    7. Create a new dashboard/table - now with dates in the new format.

    BR / Ole

    0

Please sign in to leave a comment.