SQL Server DATENAME()

SQL Server DATENAME() Function

The SQL Server DATENAME() function returns a specified part of a date as a string. This function is useful for retrieving the name of a date part, such as the day of the week or the month name.

Syntax

SELECT DATENAME(datepart, date);

The DATENAME() function takes two arguments:

  • datepart: The part of the date to return. Common values include year, quarter, month, day, week, weekday, hour, minute, second, and millisecond.
  • date: The date from which to extract the date part.

Example SQL Server DATENAME() Function Queries

Let's look at some examples of SQL Server DATENAME() function queries:

1. Getting the Month Name

SELECT DATENAME(month, '2024-06-01') AS month_name;

This query returns the name of the month for the date '2024-06-01'. The result will be:

month_name
----------
June

2. Getting the Day of the Week

SELECT DATENAME(weekday, '2024-06-01') AS day_name;

This query returns the name of the day of the week for the date '2024-06-01'. The result will be:

day_name
--------
Saturday

3. Getting the Year

SELECT DATENAME(year, '2024-06-01') AS year_name;

This query returns the year as a string for the date '2024-06-01'. The result will be:

year_name
---------
2024

4. DATENAME() with a Column

SELECT order_date, DATENAME(month, order_date) AS month_name
FROM orders;

This query returns the month name for each order_date in the orders table. The result will show the original order_date and its corresponding month_name.

5. DATENAME() with a Variable

DECLARE @date DATETIME;
SET @date = '2024-12-25';
SELECT DATENAME(month, @date) AS month_name;

This query uses a variable to store a date and then returns the name of the month. The result will be:

month_name
----------
December

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the DATENAME() function.

Step 1: Creating a Table

This step involves creating a new table named events to store some sample data with event dates.

CREATE TABLE events (
    id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATETIME
);

In this example, we create a table named events with columns for id, event_name, and event_date.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the events table.

INSERT INTO events (id, event_name, event_date) VALUES (1, 'Event 1', '2024-06-01');
INSERT INTO events (id, event_name, event_date) VALUES (2, 'Event 2', '2024-12-25');
INSERT INTO events (id, event_name, event_date) VALUES (3, 'Event 3', '2024-08-15');

Here, we insert data into the events table.

Step 3: Using the DATENAME() Function

This step involves using the DATENAME() function to return the name of the month for each event_date in the events table.

SELECT id, event_name, event_date, DATENAME(month, event_date) AS month_name
FROM events;

This query retrieves the id, event_name, event_date, and the name of the month for each row in the events table. The result will be:

id  event_name  event_date  month_name
--- ----------- ----------  ----------
1   Event 1     2024-06-01  June
2   Event 2     2024-12-25  December
3   Event 3     2024-08-15  August

Conclusion

The SQL Server DATENAME() function is a powerful tool for returning a specified part of a date as a string. Understanding how to use the DATENAME() function and its syntax is essential for effective date manipulation and data processing in SQL Server.