In SQL Server, the T-SQL DATENAME() function returns a character string that represents the specified datepart of the specified date. For example, you can pass in 2021-01-07 and have SQL Server return only the month portion (January).

The return type for DATENAME() is nvarchar.

Examples below.

Syntax

The DATENAME() syntax goes like this:

DATENAME ( datepart , date )

Where datepart is the part of date (a date or time value) for which a character string will be returned.

Example 1

Here’s a basic example where I return the month component from a date.

SELECT DATENAME(month, '2021-01-07') AS Result;

Result:

+----------+
| Result   |
|----------|
| January  |
+----------+

Example 2

In this example I assign a date to a variable, then I use multiple instances of DATEPART() to retrieve different parts of that date.

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    DATENAME(year, @date) AS Year,
    DATENAME(quarter, @date) AS Quarter,
    DATENAME(month, @date) AS Month,
    DATENAME(dayofyear, @date) AS 'Day of Year',
    DATENAME(day, @date) AS Day,
    DATENAME(week, @date) AS Week,
    DATENAME(weekday, @date) AS Weekday;

Result:

+--------+-----------+---------+---------------+-------+--------+-----------+
| Year   | Quarter   | Month   | Day of Year   | Day   | Week   | Weekday   |
|--------+-----------+---------+---------------+-------+--------+-----------|
| 2018   | 2         | June    | 153           | 2     | 22     | Saturday  |
+--------+-----------+---------+---------------+-------+--------+-----------+

Example 3

You can also retrieve the various time parts from the date.

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    DATENAME(hour, @date) AS Hour,
    DATENAME(minute, @date) AS Minute,
    DATENAME(second, @date) AS Second,
    DATENAME(millisecond, @date) AS Millsecond,
    DATENAME(microsecond, @date) AS Microsecond,
    DATENAME(nanosecond, @date) AS Nanosecond;

Result:

+--------+----------+----------+--------------+---------------+--------------+
| Hour   | Minute   | Second   | Millsecond   | Microsecond   | Nanosecond   |
|--------+----------+----------+--------------+---------------+--------------|
| 8      | 24       | 14       | 311          | 311204        | 311204200    |
+--------+----------+----------+--------------+---------------+--------------+

Example 4

You can also retrieve the TZoffset and the ISO_WEEK datepart.

The TZoffset is returned as the number of minutes (signed). The ISO_WEEK datepart refers to the ISO week-date system (part of the ISO 8601 standard) which is a numbering system for weeks.

DECLARE @date datetimeoffset = '2018-06-02 08:24:14.3112042 +08:00';
SELECT 
    DATENAME(TZoffset, @date) AS 'Time Zone Offset',
    DATENAME(ISO_WEEK, @date) AS 'ISO_WEEK';

Result:

+--------------------+------------+
| Time Zone Offset   | ISO_WEEK   |
|--------------------+------------|
| +08:00             | 22         |
+--------------------+------------+

In this example, I set the date to a datetimeoffset format, which includes the timezone offset. The TZoffset is returned in minutes.

The results of DATENAME() will often resemble the results from DATEPART() (due to most date parts being numeric). However, DATEPART() returns an integer as opposed to a character string. Therefore DATEPART() will return months (and the weekday component) in numeric form (e.g. 1 instead of January).

Getting the Short Month Name

If you need the month name, but in its shortened, 3 letter abbreviation form (for example, Jan instead of January), check out my article 5 Ways to Get the Short Month Name from a Date in SQL Server.