Carole,
Instead of using CTOD(...) to create date you always could use DATE() function:
? DATE(2007,12,31)
That way you have a big advantage - this function is not SET DATE dependable.


>My formula was for the format mm/dd/yyyy, I'll bet your working with dd/mm/yyyy. In that case you should do:
>
>

? left(cmonth(CTOD([01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4))),3) + [ - ] + RIGHT(m.lcdate,4)
>
>*To use the cmonth function you need a complete date, and since you were
>*missing day of the month, you fill in day with "01", but since we are in
>*different countries the standard is different.
>
>* Step-by-step here is what is happening
>
>* this gives you the month
>? LEFT(m.lcdate,2)
>
>* this gives you the year
>? RIGHT(m.lcdate,4)
>
>* this creates a character string in the date format adding in the first of the month
>? [01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4)
>
>* this converts the characther string into a date type
>? CTOD([01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4))
>
>* this gets the alpha month
>? cmonth(CTOD([01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4)))
>
>* this truncates the month to the first 3 characters
>? left(cmonth(CTOD([01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4))),3)
>
>* this adds the dash back in
>? left(cmonth(CTOD([01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4))),3) + [ - ]
>
>* this tacks the year on to the end
>? left(cmonth(CTOD([01/]+LEFT(m.lcdate,2)+[/]+RIGHT(m.lcdate,4))),3) + [ - ] + RIGHT(m.lcdate,4)
>

>
>Oh and don't call me dear. :)
>
>>Hello Dear
>>
>>07-2006 shows Jan-2006 due to this word in forumula
>>[/01/]
>>
>>Please correct
>>I think month number should not given in formula,
>>If I use this function
>>
>>LEFT(CMONTH(CTOD(LEFT(pay.month,2)+alltrim(substr(pay.month,2))+RIGHT(pay.month,4))),3)+[ - ]+RIGHT(pay.month,4)
>>
>>Then it display
>>*ba - 2006
>>
>>Please help