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?

回答1:

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.

回答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)

回答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

回答4:

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

Output:

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