How to change mm/dd/yyyy to desired format

Question

hi guys, I have date in format mm/dd/yy.For eg:4/1/2009 means April 1 2009.I want to get format as April 1,2009 in sql.Can anybody help?

Answer 1

Use the CONVERT function.

http://msdn.microsoft.com/en-us/library/ms187928.aspx

It looks like format 107 is the one you want.

Answer 2

See this link for SQL commands to convert date and time formats.

Answer 3

This should do what you want.

DECLARE @date datetime
SET @date = getdate()
SELECT datename(day,@date) + ' ' + left(datename(month,@date),3) + ' ' + datename(year,@date)

Answer 4

declare @d datetime
select @d = '20090401'

select convert(varchar(50),@d,107)

will give you this

Apr 01, 2009

If you want 1 instead of 01 do this

declare @d datetime
select @d = '20090401'

select replace(convert(varchar(50),@d,107),' 0',' ')

Apr 1, 2009