Datetime display using month name in T-SQL

There is a field which is date type in a table. The form开发者_如何学JAVAat of this field is mm/dd/yy. Is there any way to convert it to dd-Month name-yy?

Best Regards,

Without any hassle, you can use CONVERT to get "dd MONTHNAME yyyy" format:

SELECT CONVERT(VARCHAR, GETDATE(), 106)

e.g. "25 Jan 2010"

If you want your exact format, you may need a bit of manual fiddling around like:

SELECT CAST(DAY(GETDATE()) AS VARCHAR) +  '-' + LEFT(DATENAME(mm, GETDATE()), 3) + '-' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR), 2)

e.g. "25-Jan-10"

Update:
In fact, a shorter way to achieving this is:

SELECT REPLACE(CONVERT(VARCHAR, GETDATE(), 6), ' ', '-')

e.g. "25-Jan-10"

Take a look at the CAST and CONVERT functions:

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

example:

select convert( char(8), getdate(), 5 ) 

output:

dd-mm-yy

Source: compuspec date format conversion

you can try convert() ?

Maybe you need to differentiate between storing datetime values in SQL Server and displaying them. Here is a great article that explains what's going on behind the scenes: The ultimate guide to the datetime datatypes