I think you are actually talking about Character fields instead of Date Fields (even though the string inside the field represents a date). Please correct me if I'm mistaken there.
If this is a string, then you will need to add one calculated field to insert the dash.
left(DateField,2) + "-" + Right(DateField,len(DateField) - 2)
I'm assuming here that you have leading zeros for days 1 - 9 in the month. If not, the expresion would be a little more complex.
If the format is always 5 characters - i.e. for the 1st of a month you see 01MMM - then you can simply create a calculated field something like
If it is not always 5 characters BUT the month is always 3 characters then something like
should do it.
You may need some TRIM() function activity if you come across leading or trailing spaces in the field that affect the way the formula presents its results.
Edit: Ah! Parallel posting. Joey is quite right to point out that you are dealing with a character field rather than a date.
Perhaps i am confusing the issue on this.....
The date in my template is currently a CHaracter field and i want to convert the Current format to a date field so that i can sort dates. By using the above calculated field to create a new Date field means that Data gets sorted by the numerical value and not as dd-mmm date.
Sort data shows as
Thanks for all your help on this.
You will need a YEAR from somewhere for a date field.
If you have a source you can concatenate the year onto the end of you DD-MMM and then convert it to a date using the CtoD() function.
Check out the Help file for supported date formats.
If you want to sort on what you have then separate the component parts into two fields, give the Month a number as an alternative and sortable (but possibly hidden field) value and use both fields for sorting your table/summary.
It's not confusing but we do need to know what you are setting out to do to be able to offer useful advice.
If you can safely assume that the dates in question occur in the same year as the year in which you're using the model (right now), you could use this expression to generate your proper date field:
Otherwise you could build some logic that would do something like if the current month is January then the year for the dates is (this year - 1).
A better way would be to extract the reporting year from the data source itself, if possible.