How to change mm/dd/yyyy to desired format

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?

  • Sum a union query
  • WSDL on SQL Server gives HTTP status 505 Version Not Supported
  • Select rows containing the group-wise maximum of a column
  • SQL Subtract exactly a year
  • How to return the value that exists in SQL Server XPath?
  • SQL Server 2014 equivalent to mysql's find_in_set()
  • SQL Server 2008 R2: Prepare Dynamic WHERE Clause
  • Alternatives to GUID for clustered index
  • SQL Server after update trigger
  • Inserting multiple select statements into a table as values
  • T-SQL Round to decimal places
  • Normalizing an extremely big table
  • 3 Solutions collect form web for “How to change mm/dd/yyyy to desired format”

    Use the CONVERT function.

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

    It looks like format 107 is the one you want.

    This should do what you want.

    DECLARE @date datetime
    SET @date = getdate()
    SELECT datename(day,@date) + ' ' + left(datename(month,@date),3) + ' ' + datename(year,@date)
    
    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.