Datetime display using month name in T-SQL

Question

Hi all

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

you can try convert() ?

Answer 2

Take a look at the CAST and CONVERT functions:

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

Answer 3

example:

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

output:

dd-mm-yy

Source: compuspec date format conversion

Answer 4

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"

Answer 5

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