Is there a way to get dates with custom formats in

问题:

In Oracle, you can use:

SELECT to_char(sysdate, 'yyyy-mm') FROM dual;

to show just the year and month portion of a date. Additionally, you can set NLS_DATE_FORMAT to change the way dates are returned by default.

I know in SQL Server you have a set of predefined options you can use:

SELECT convert(varchar, getdate(), 110) –- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) –- yyyy/mm/dd

But is there an option that gives me the same freedom as Oracle's to_char? Preferably one that doesn't require me to create a custom function.

回答1:

Currently the best option is to have a CLR function that is a wrapper to .NET's DateTime.ToString(string format).

If you don't want a separate function, you can build the required string from pieces:

YEAR(@d) + '-' + MONTH(@d) + '-' + DAY(@d)

As for the definite solution, there will be a formatting function in the next version of SQL Server.

回答2:

It depends on what version of SQL Server you are using.

SQL Server 2005, 2008, and 2008 R2

For these versions you would need to make use of SQLCLR to expose that functionality from .NET. You can either write your own SQLCLR function using the DateTime class (as noted in @GSerg's answer), or you can simply download and install the Free version of SQL# (which I am the author of, but the Date_Format function is free).

Example:

SELECT SQL#.Date_Format(GETDATE(), 'dddd in MMMM', '') AS [Default_language],
       SQL#.Date_Format(GETDATE(), 'dddd in MMMM', 'he') AS [Hebrew],
       SQL#.Date_Format(GETDATE(), 'dddd in MMMM', 'de') AS [German];

Returns:

Default_language      Hebrew               German
Monday in January     יום שני in ינואר    Montag in Januar

SQL Server 2012 and newer

Use the built-in FORMAT function.

Example:

SELECT FORMAT(GETDATE(), 'dddd in MMMM') AS [Default_language],
       FORMAT(GETDATE(), 'dddd in MMMM', 'he') AS [Hebrew],
       FORMAT(GETDATE(), 'dddd in MMMM', 'de') AS [German];

Returns:

Default_language      Hebrew               German
Monday in January     יום שני in ינואר    Montag in Januar

For both SQLCLR and built-in FORMAT options

The following two MSDN pages detail the available formatting options:

• Custom Date and Time Format Strings
• Standard Date and Time Format Strings