Sorting Date

[#1]

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..!!!

[MK_QSL]

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

[alfredlee]

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)

[MarcoWedel]

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

Try

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.

regards

Marco