question

'n/a' isn't a date, so can't be assigned to the same variable that would normally hold a date or datetime value. You'll need to use a string variable and return the date of the previous Sunday as a string by using convert(). That way you can return 'n/a' on the other days without an error - [ http://msdn.microsoft.com/en-us/library/ms187928.aspx][1] ... THEN convert(varchar(20), dateadd(dd, (-1)*(datepart... WHEN datepart(dw,@Editiondate) <> 1 then 'n/a' [1]: http://msdn.microsoft.com/en-us/library/ms187928.aspx

You can return NULL instead of N/A. As mentioned by KenJ, 'n/a' is character value and cannot be assigned to datetime variable. select @CurrentSun = (CASE WHEN datepart(dw,@Editiondate) = 1 Then dateadd(dd, (-1)*(datepart(dw, @Editiondate)-1) , @Editiondate) WHEN datepart(dw,@Editiondate) 1 then NULL

When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST. I recomment to use this setting to validate the assumption day 1 = sunday (and you'll know: "assumption is the mother of all ...")

Thank you, I signed that part to Null and then used case: case when @CurrentSun is null then 'n/a' else cast(datepart(mm,@CurrentSun)as char(2)) + '-' + cast(datepart(dd,@CurrentSun)as char(2)) + ' ' + 'Su BD' end as SuBD ,