AX / Dynamics 365 For Finance and Operations blog

The FORMAT() Function

The FORMAT() function returns a value formatted in the specified format and optional culture. You can use it to return the month name from a date.

Here’s an example:

DECLARE @date datetime2 = '2018-07-01';
SELECT FORMAT(@date, 'MMMM') AS Result;

Result:

+----------+
| Result   |
|----------|
| July     |
+----------+

In this case we provided a format string of MMMM which is a custom date and time format string for returning the month name.

The good thing about the FORMAT() function is that it accepts an optional argument that allows you to specify the culture to use.

Here are some examples:

DECLARE @date datetime2 = '2018-07-01';
SELECT 
    FORMAT(@date, 'MMMM', 'en-US') AS 'en-US',
    FORMAT(@date, 'MMMM', 'es-es') AS 'es-es',
    FORMAT(@date, 'MMMM', 'de-de') AS 'de-de',
    FORMAT(@date, 'MMMM', 'zh-cn') AS 'zh-cn';

Result:

+---------+---------+---------+---------+
| en-US   | es-es   | de-de   | zh-cn   |
|---------+---------+---------+---------|
| July    | julio   | Juli    | 七月    |
+---------+---------+---------+---------+

If you don’t provide the culture argument, the language of the current session is used. If you’re not sure what language the current session is using, here are 3 ways to get the language of the current session. If you find that you need to change the language, here’s how to set it to another language.

You can also use the FORMAT() function to get the abbreviated month name. To do this, simply use MMM (three Ms instead of four).

Here’s an example that returns both the abbreviated month name, and the full month name:

DECLARE @date datetime2(0) = '2028-09-01';
SELECT 
    FORMAT(@date, 'MMM', 'en-US') 'en-US',
    FORMAT(@date, 'MMM', 'es-es') 'es-es',
    FORMAT(@date, 'MMM', 'it') 'it'
UNION ALL
SELECT 
    FORMAT(@date, 'MMMM', 'en-US'),
    FORMAT(@date, 'MMMM', 'es-es'),
    FORMAT(@date, 'MMMM', 'it');

Result:

+-----------+------------+-----------+
| en-US     | es-es      | it        |
|-----------+------------+-----------|
| Sep       | sep.       | set       |
| September | septiembre | settembre |
+-----------+------------+-----------+