Hi David,

You can CAST again to get desired result or

<pre>...
        SELECT CONVERT(char(4), @dinvoice, 111) AS csalesyear,
	        CONVERT(char(2), @dinvoice, 101) AS csalesmonth
...

>
>I have one more problem! I have it working now with the derived table. But the problem is that the second column, the salesmonth, is getting returned to a vfp cursor as a memo field, the salesyear is being returned as char(30).
>
>If I run it in QA and select the results into a table, the datatype for the second column is varchar(8000). The complex expression with the cast and replicate is to mimic padl(). (as per one of your threads - I borrowed from your solution or someone elses)
>
>But shouldn't the 'cast as char' force it to char and therefore to vfp as a regular character field? Maybe concatenating two chars - sql server doesn't know that the result won't be too big for char, so it makes it varchar?
>
>Anyway, how can I get this expression back down to char, not varchar.
>
>Thanks again,
>David
>
>
>>You have to use the same expression as in column list or you can use derived table

>>SELECT * FROM (	
>>	select cast(year(dinvoice)as char) as csalesyear,
>>			replicate('0', 2 - len(cast(month(dinvoice) as char) )) +
>>				cast(month(dinvoice) as char) as csalesmonth
>>		from arinvc
>>		left outer join aritrs on 
(arinvc.ccustno = aritrs.ccustno and arinvc.cinvno = aritrs.cinvno)
>>		where  arinvc.lvoid = 0 and arinvc.cinvno <> 'BAL F/W' and
>>				(ARINVC.CSLPNNO = 'CAROL' 
and ARINVC.DINVOICE >= '2006-01-01' and ARINVC.DINVOICE <= '2007-01-31'
>>				and ARITRS.CITEMNO IN (SELECT citemno 
from sr_items_in_group where citemgroup='PROSTATFAMILY'))
>>				) dt1
>>group by csalesyear, csalesmonth	
>>order by csalesyear, csalesmonth
>>

>>