Customization On Sql Datetime Format How?

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?

Solution 1:

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

selectsubstring(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.

Solution 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()) ASVARCHAR(3)) +' '+ DATENAME(DD,GETDATE())   

You can also user convert function for other formats like :

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

Solution 3:

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

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

Solution 4:

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

Output:

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