customization on sql datetime format how?

Question

I need to convert date time to nvarchar on sql server like 2011 Oct 24 but I'm not able to convert into this. Can anyone help?

Answer 1

SELECT CONVERT(nvarchar(10), getdate(), 102)

You can find all of the conversion codes here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 2

For, exact format you required, you need to get all parts(day,month and year) of date seperately like :

SELECT DATENAME(YYYY,GETDATE()) + ' ' + CAST(DATENAME(MM,GETDATE()) AS VARCHAR(3)) + ' ' + DATENAME(DD,GETDATE())   

You can also user convert function for other formats like :

SELECT CONVERT(VARCHAR(20),GETDATE(),106)

Answer 3

WITH d (d) AS (SELECT GETDATE())
SELECT DATENAME(YEAR, d) + ' ' + CONVERT(NVARCHAR(6), d, 109)
FROM d

Output:

-------------------------------------
2011 Oct 24

Answer 4

There's no format that matches exactly. You can use substring to assemble the format manually:

select  substring(convert(varchar(20),getdate(),106),8,4) + ' ' +
        substring(convert(varchar(20),getdate(),106),4,3) + ' ' +
        substring(convert(varchar(20),getdate(),106),1,3)

N.B. Doing formatting in SQL is much harder than client-side, in C# or Java or Ruby.