SQL Convert style values

I am trying to convert from "July 30, 2004" to "JULY 30, 2004".  I have the code for the short date i.e. JUL 30, 2004 but I need the month spelled out.  This date field is being built in a work table inside a stored procedure then it is passed into Crystal Reports v8.0.  I would like to do all the date formating inside the stored procedure so I don't run into Crystal issues down the road when I do my upgrade project.

Here is what I am doing so far:

This Outputs: JUL 30, 2004 I want JULY 30, 2004

Is there an undocumented style number that will do this or do I need to perform some crazy formating or write a case statement?

Any help would be appreicated.

Who is Participating?
MartinCMSConnect With a Mentor Commented:
try this...

UPPER(DATENAME(month, getdate())) + ' ' + right('00'+Convert(varchar(2),DAY(getdate())),2) + ', ' + Convert(varchar(4),YEAR(getdate()))

right('00'  - function add zero infront for day between 1-9
Convert(varchar(2) - function will convert day digit into varchar to avoid miss match
Convert(varchar(4) - function will convert year digits into varchar to avoid miss match

Select UPPER(DATENAME(month, getdate())) + ' ' + right('00'+Convert(varchar(2),DAY(getdate())),2) + ', ' + Convert(varchar(4),YEAR(getdate()))

result: NOVEMBER 21, 2004
Maybe this?

UPPER(DATENAME(month, #rptrows.print_date)) + ' ' + DAY(#rptrows.print_date) + ', ' + YEAR(#rptrows.print_date)
Your field seems to be in string format since you're converting it to datetime and then back to a varchar. You're better off leaving it like a date field and passing it on to Crystal Reports as a datetime value (don't use smalldatetime, problems), since Crystal has excellent customizable field displays (Format Field...). Another advantage being, the field can be used natively as a datetime by any application.  
All Courses

From novice to tech pro — start learning today.