When working with dates in SQL Server, sometimes you might find yourself reaching for the DATEPART() function, only to realise that what you really need is the DATENAME() function. Then there may be other situations where DATEPART() is actually preferable to DATENAME().

So what’s the difference between the DATEPART() and DATENAME() functions?

Let’s find out.

Definitions

The difference between these two functions is in their definitions:

DATEPART()

Returns an integer that represents the specified datepart of the specified date.

DATENAME()

Returns a character string that represents the specified datepart of the specified date

According to their definitions, the only difference between these two functions is the return type:

• DATEPART() returns an integer.
• DATENAME() returns a string.

So that’s the difference.

In both definitions, datepart is the part of the date you want (e.g. month), and date is the date you want the datepart returned from.

Month and Day Names

The most obvious example where DATENAME() is more suitable to DATEPART() is when you want the day or month name to be returned.

Here’s an example.

DATENAME()

Here’s what DATENAME() returns when we want the weekday and month from a date:

SELECT
    DATENAME(weekday, '2000-01-01') AS 'DATENAME Weekday',
    DATENAME(month, '2000-01-01') AS 'DATENAME Month';

Result:

+--------------------+------------------+
| DATENAME Weekday   | DATENAME Month   |
|--------------------+------------------|
| Saturday           | January          |
+--------------------+------------------+

DATEPART()

Here’s what DATEPART() returns:

SELECT
    DATEPART(weekday, '2000-01-01') AS 'DATEPART Weekday',
    DATEPART(month, '2000-01-01') AS 'DATEPART Month';

Result:

+--------------------+------------------+
| DATEPART Weekday   | DATEPART Month   |
|--------------------+------------------|
| 7                  | 1                |
+--------------------+------------------+

When Results are the Same

In most cases, the results from both functions will appear to be the same. This is because most date parts are numeric by their very nature. For example:

SELECT
    DATEPART(year, '2000-01-01') AS DATEPART,
    DATENAME(year, '2000-01-01') AS DATENAME;

Result:

+------------+------------+
| DATEPART   | DATENAME   |
|------------+------------|
| 2000       | 2000       |
+------------+------------+

However, as mentioned, one returns an integer and the other returns a string.

We can see evidence of that with the following example:

SELECT
    DATEPART(year, '2000-01-01') + '1' AS DATEPART,
    DATENAME(year, '2000-01-01') + '1' AS DATENAME;

Result:

+------------+------------+
| DATEPART   | DATENAME   |
|------------+------------|
| 2001       | 20001      |
+------------+------------+

The plus sign is an arithmetic operator on numeric data types, but it is a string concatenation operator on strings.