Date function in SQL

Look at the styles option of CONVERT

If you are storing the date in a datetime field, then use this

declare @MyDate datetime
set @MyDate = '1 may 2011'

select 
 @MyDate, 
 substring(CONVERT(varchar, @MyDate, 106), 4, LEN(CONVERT(varchar, @MyDate, 106)))

Otherwise if it is a string, convert it to a date first...

declare @MyDate2 varchar(10)
set @MyDate2 = '05-01-2011'

select 
   @MyDate2,
   CONVERT(datetime, @MyDate2, 110),
   substring(CONVERT(varchar, CONVERT(datetime, @MyDate2, 110), 106), 4, LEN(CONVERT(varchar, CONVERT(datetime, @MyDate2, 110), 106))),
   DATENAME(m, @MyDate2) + ' ' + DATENAME(yy, @MyDate2)

It goes without saying, though, that SQL isn't the best place to be formatting your output, and it should be handled by the presentation layer.

There'll be a few ways to do it. CONVERT allows you to format dates into various formats, but there isn't a style that's exactly what you want. So one way is:

SELECT DATENAME(month, GETDATE()) + ',' + CAST(YEAR(GETDATE()) AS CHAR(4))

EDIT

Since you're using DB2, here's what I think will work, with the reservation that I'm not a DB2 developer.

• Instead of DATENAME(Month,@dt) you can use MONTHNAME(@dt)
• Instead of DATANEM(Year,@dt) you can use CHAR(YEAR(@dt))

If you have your dates in column dt of table T1 in schema S1:

SELECT MONTHNAME(dt) + ', ' + CHAR(YEAR(dt)) FROM S1.T1

END EDIT

When you say the date is stored as mm-dd-yyyy, do you mean that it's stored as a varchar datatype? Otherwise it's stored as a datetime or date datatype and may be presented as for example mm-dd-yyyy.

Anyway, if you have it as a varchar, you can cast it to datetime, and from that get the Monthname and year:

SET DATEFORMAT mdy
GO
DECLARE @dt datetime
SET @dt='06-09-2011'
SELECT DATENAME(Month,@dt) + ', ' + DATENAME(Year, @dt)

Will produce June, 2011 as a result