How do you change the date format?

[dan205]

I import dates via a data connection from a SQL Server database that formats the date field as YYYY-DD-MM. The DD part is always '01'. The date field is called PERIOD and my load script just loads it as is.

If I add a table with columns for PERIOD and Month(PERIOD), it looks like this:

How do I format the date as DD-MM-YYYY and list the month?

Also, I want to create a filter that lists the distinct dates in descending order (i.e. most recent first), and preferably in the format MMM YYYY (e.g. APR 2023). Is that possible? I can't work out how to do it.

Thanks in advance.

Dan

[sandeep-singh]

Date(PERIOD, 'DD-MM-YYYY')  for Date format 

Date (PERIOD, 'MMM YYYY') and sort it numerically 

[dan205]

Thanks Sandeep but that doesn't work - I get an 'error in expression' for Date(PERIOD, DD-MM-YYYY).

[sandeep-singh]

you missed adding an apostrophe in the date format - Date(PERIOD, 'DD-MM-YYYY')

[BrunPierre · Accepted solution]

Hi, 

Format conversion: Date(Date#(PERIOD,'YYYY-DD-MM'),'DD/MM/YYYY')

Month Year creation: MonthName(Date#(PERIOD,'YYYY-DD-MM'))

Sort by expression (Desc) in Filter: Floor(MonthName(Date#(PERIOD,'YYYY-DD-MM')))

[dan205]

Thanks BrunPierre, those expressions work great. I really appreciate your help.

Kind regards,

Dan