Datetime display using month name in T-SQL

Question

There is a field which is date type in a table. The format of this field is mm/dd/yy. Is there any way to convert it to dd-Month name-yy?

Best Regards,

Answer 1

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"