Date Time Format in SQL Server

Question

Can any one guide me to get below date Format?

 18th Mar 2014

I do see other date formats are supported. But nd, th after date is needed for me.

Answer 1

Maybe not the simplest way, but this should do it;

SELECT CAST(DATEPART(d, dt) AS NVARCHAR(2)) + 
  CASE DATEPART(d, dt)  WHEN 1  THEN 'st' WHEN 2 THEN 'nd'
                        WHEN 3  THEN 'rd' WHEN 21 THEN 'st'
                        WHEN 22 THEN 'nd' WHEN 23 THEN 'rd' 
                        WHEN 31 THEN 'st' ELSE 'th' END +
  CAST(SUBSTRING(CONVERT(NVARCHAR(256), dt, 106), 3, 256) AS NVARCHAR(256))
AS [myDate]
FROM test;

An SQLfiddle to test with.

Answer 2

Try this

SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), Left(CONVERT(VARCHAR(11), GETDATE(), 106),2), Left(CONVERT(VARCHAR(11), GETDATE(), 106),2) + 'th') AS [DD Mon YYYY]

Fiddle Demo