Skip to main content
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting Date

I have a column with unsorted Dates in the format 'DD.MM.YYYY hh:mm:ss'. I would like to extract Date from this data in the format 'MM.YYYY' and also in the sorted order. I'm using Date(MonthStart(DAT), 'MM.YYYY'), where DAT is the name of my column. I'm getting the date in 'MM.YYYY' format but I'm not able to get it in sorted form. I'm using this extracted format in a chart and I tried to sort by setting Expression 'DAT' as ascending in the sort tab of chart. But no success so far.

Please help me in achieving this. Thanks in advance..!!!

3 Replies

Use below code in your script.. .Change necessary field names...

DUAL(Date(Floor(TimeStamp#(DATE,'DD.MM.YYYY hh:mm:ss')),'MM.YYYY'),Date(Floor(TimeStamp#(DATE,'DD.MM.YYYY hh:mm:ss')),'YYYYMM')) as RequiredDate
Contributor III
Contributor III

Can you try to sort by the expression Year(DAT) * 100 + Month(DAT)?

In case the DAT is not a real date field (i.e. Text), you can use Mid to extract the corresponding section (e.g. MID(DAT,7,4) for Year and MID(DAT,4,2) for Month)



Your DAT field is not loaded as Timestamp but as Text.


Date(MonthStart(TimeStamp#(DAT, 'DD.MM.YYYY hh:mm:ss')),'MM.YYYY')

or better load your DAT field with

TimeStamp#(DAT, 'DD.MM.YYYY hh:mm:ss') as DAT

then your expression

Date(MonthStart(DAT), 'MM.YYYY')

will work too.