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.