Max date from a string

[#1]

Hi I have a source file which stores dates as in this format '2000 Jan'.

Now I want to find the latest date so I use Max(datefield)

But all I get is a blank value. Any help?

I am guessing that it could be considering my field as string and hence the max function isn't working..

[tresesco · Accepted solution]

Try:

Date(Max(Date#(datefield, 'YYYY MMM')))

[jagan]

Hi,

Try like this

Date(Max(Date#(DateDimensionName, 'YYYY MMM')))

Hope this helps you.

Regards,

Jagan.

[morganaaron]

If you need to keep the same format (Year and Month only) you could use either:

MonthName(Max(Date#(Datefield, 'YYYY MMM')))

or

Year(Max(Date#(Date, 'YYYY MMM'))) & ' ' & Month(Max(Date#(Date, 'YYYY MMM')))

[MarcoWedel]

MonthName(Max(Date#(Datefield, 'YYYY MMM')))

is the better choice, because it keeps the numerical value, so no sorting or calculation issues.

hope this helps

regards

Marco

[jagan]

Hi,

If you got the solution please close this thread by giving Correct and Helpful Answers.

Regards,

Jagan.