Date conversion 1

[northw]

Hi all,

I have string field month description from database, where the data is like Jan-11, I want to convert into 2011-01.
I have tried something like this.
@YearMonth
Year(cdate({Query1.Month Description}))&'-'&Month(cdate({Query1.Month Description}))
I have ended up getting 2011.00-1.00, And I can't format this.
Please advice any changes to the formula.
Thanks in advance.

 

[northw]

Figured it out.
@Yearmonth
totext(date({Query1.Month Description}), 'yyyy-MM')

Thanks.

 

[northw]

Just noticed:
Using this formula
@Yearmonth
totext(date({Query1.Month Description}), 'yyyy-MM')
It's returning all the date with year 2011, as there is data for previous year as well.

 

[lbass]

It is interpreting the "11" as the day, not the month. Try the following:

stringvar x := {table.datestring};
stringvar y := left(x,3)+'-'+'01'+'-'+right(x,2);
date

Then right click on the formula->format field->date->select "Mar-99".

-LB

 

[northw]

Thanks lBass.
But I am using this field in a chart, The chart has to display jan-11, dec-10, nov-10 so on (Current month to last six months), In order to display that I am using specified sort and adding the month details in the sort, I just can't keep track of that and keep on adding, So I want to convert that field to "YYYY-MM"(2011-01, 2010-12, 2010-11 so on...) format and make it Desc, So that I don't have to take care of it later.
So I cant use format field in a chart. Any other work around...to fix this?
Thanks lBass for you time.

 

[lbass]

Well, then just use:

stringvar x := {table.datestring};
stringvar y := left(x,3)+'-'+'01'+'-'+right(x,2);
totext(date,"yyyy-MM")

-LB

 

[northw]

Thank You LBass.