SQL Server DATEPART()

SQL Server DATEPART() Function

The SQL Server DATEPART() function returns an integer representing the specified part of a date. This function is useful for extracting specific parts of a date, such as the year, month, day, hour, minute, or second.

Syntax

SELECT DATEPART(datepart, date);

The DATEPART() 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 DATEPART() Function Queries

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

1. Extracting the Year

SELECT DATEPART(year, '2024-06-01') AS year_part;

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

year_part
---------
2024

2. Extracting the Month

SELECT DATEPART(month, '2024-06-01') AS month_part;

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

month_part
----------
6

3. Extracting the Day

SELECT DATEPART(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

4. DATEPART() with a Column

SELECT order_date, DATEPART(hour, order_date) AS hour_part
FROM orders;

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

5. DATEPART() with a Variable

DECLARE @date DATETIME;
SET @date = '2024-06-01 12:34:56';
SELECT DATEPART(minute, @date) AS minute_part;

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

minute_part
------------
34

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the DATEPART() 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 10:00:00');
INSERT INTO events (id, event_name, event_date) VALUES (2, 'Event 2', '2024-12-25 14:00:00');
INSERT INTO events (id, event_name, event_date) VALUES (3, 'Event 3', '2024-08-15 09:30:00');

Here, we insert data into the events table.

Step 3: Using the DATEPART() Function

This step involves using the DATEPART() function to extract the hour part of the event_date column for each record in the events table.

SELECT id, event_name, event_date, DATEPART(hour, event_date) AS hour_part
FROM events;

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

id  event_name  event_date            hour_part
--- ----------- -------------------  ---------
1   Event 1     2024-06-01 10:00:00  10
2   Event 2     2024-12-25 14:00:00  14
3   Event 3     2024-08-15 09:30:00  9

Conclusion

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