Using DATEPART Function in SQL: Real-Life Examples

Ever wondered how to extract just the month, day, or year from a date in SQL? Good news! You don’t need magic. You only need the DATEPART function.

SQL’s DATEPART() is like a date detective. It helps you pull specific parts from a date or time. Let’s break it down and make it super simple!

What is DATEPART?

DATEPART is a function in SQL that lets you pluck a piece from a full date. Want just the week of the year? No problem. Only care about the hour? DATEPART has you covered.

DATEPART(part, date)

part is what you want to extract (like year, month, day).
date is the column or date value you’re working with.

Common “parts” you can extract

year – Pulls just the year (e.g. 2024)
month – Gets the month number (e.g. 6 for June)
day – The day of the month
weekday – The day of the week (Sunday = 1)
hour, minute, second – Time parts
quarter – Returns the quarter (1 to 4)
week – Week of the year

Sounds cool, right? Let’s jump into some real-life examples to see this in action.

Example 1: Find Everyone Born in June

Imagine you’re working with an employee database. You want to send birthday cards to people born in June.

SELECT name, birthdate 
FROM employees 
WHERE DATEPART(month, birthdate) = 6;

That’s it! You pull all rows where the birthday month is 6 — aka June.

[ai-img]sql, birthday month, employee data[/ai-img]

Example 2: Analyze Sales by Quarter

Let’s say you’re working in a sales department. The team wants to know how much was sold in each quarter. Here’s what you’d do:

SELECT 
  DATEPART(quarter, sale_date) AS Quarter, 
  SUM(total_amount) AS TotalSales 
FROM sales 
GROUP BY DATEPART(quarter, sale_date);

This organizes all sales into four buckets by quarter: Q1, Q2, Q3, and Q4. The finance team will love you!

Example 3: Count Logins Per Day of the Week

Let’s make it fun. Want to know which days people log in the most? Use weekday with DATEPART!

SELECT 
  DATEPART(weekday, login_time) AS DayOfWeek, 
  COUNT(*) AS Logins 
FROM user_logins 
GROUP BY DATEPART(weekday, login_time);

Now you’ll see how login activity varies by day. Monday blues or weekend spikes?

[ai-img]sql, login data, weekdays, user behavior[/ai-img]

Bonus Trick: Use DATEPART with GETDATE()

GETDATE() returns the current date and time. Pair it with DATEPART to learn things about right now.

SELECT DATEPART(hour, GETDATE()) AS CurrentHour;

This tells you what hour it is. Great for real-time reporting or time-based actions!

But Be Careful!

While DATEPART is helpful, don’t forget about:

Performance: Using DATEPART on indexed date columns in WHERE clauses can slow things down.
Weekday Numbers: They start with Sunday as 1. That can be tricky depending on your locale.