SQL Server DAY()

SQL Server DAY() Function

The SQL Server DAY() function returns an integer representing the day part of a date. This function is useful for extracting the day from a date value.

Syntax

SELECT DAY(date);

The DAY() function takes a single argument:

  • date: The date from which to extract the day part.

Example SQL Server DAY() Function Queries

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

1. Basic DAY() Example

SELECT DAY('2024-06-01') AS day_part;

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

day_part
--------
1

2. Extracting the Day from a DateTime

SELECT DAY('2024-06-01 12:34:56') AS day_part;

This query returns the day part of the datetime '2024-06-01 12:34:56'. The result will be:

day_part
--------
1

3. DAY() with a Column

SELECT order_date, DAY(order_date) AS day_part
FROM orders;

This query returns the day part of the order_date column for each record in the orders table. The result will show the original order_date and its corresponding day_part.

4. DAY() with a Variable

DECLARE @date DATETIME;
SET @date = '2024-06-01';
SELECT DAY(@date) AS day_part;

This query uses a variable to store a date and then returns the day part. The result will be:

day_part
--------
1

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the DAY() 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 DAY() Function

This step involves using the DAY() function to return the day part of the event_date column for each record in the events table.

SELECT id, event_name, event_date, DAY(event_date) AS day_part
FROM events;

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

id  event_name  event_date  day_part
--- ----------- ----------  --------
1   Event 1     2024-06-01  1
2   Event 2     2024-12-25  25
3   Event 3     2024-08-15  15

Conclusion

The SQL Server DAY() function is a powerful tool for extracting the day part of a date as an integer. Understanding how to use the DAY() function and its syntax is essential for effective date manipulation and data processing in SQL Server.