Date functions (DATEADD, DATEDIFF, EXTRACT)

Overview

Date Functions — DATEADD, DATEDIFF, EXTRACT

Date functions help you work with dates and time in SQL.

Beginners commonly use them to:

• Add or subtract days, months, or years
• Calculate the difference between two dates
• Pull out parts of a date (year, month, day)

These functions make date-based analysis much easier.

---

DATEADD — Add or Subtract Time

DATEADD lets you add or subtract a time unit (days, months, years) from a date.

DATEADD(unit, number, date)
  

Common units:

• DAY
• MONTH
• YEAR

Example 1: Add 7 days to a date

SELECT DATEADD(DAY, 7, '2024-09-01') AS NewDate;
  

Explanation

• Takes the date '2024-09-01'
• Adds 7 days
• Returns '2024-09-08'

---

Example 2: Add 1 year to a course start date

SELECT course_name,
       DATEADD(YEAR, 1, start_date) AS NextYearStart
FROM courses;
  

Useful for programs that repeat yearly.

---

DATEDIFF — Find the Difference Between Two Dates

DATEDIFF tells you how many days, months, or years exist between two dates.

DATEDIFF(unit, start_date, end_date)
  

Example 3: Days between enrollment date and today

SELECT student_id,
       course_id,
       DATEDIFF(DAY, enrollment_date, GETDATE()) AS DaysSinceEnrollment
FROM enrollments;
  

Shows how long ago a student enrolled.

---

Example 4: Months between course start and end dates

SELECT course_name,
       DATEDIFF(MONTH, start_date, end_date) AS CourseLengthMonths
FROM courses;
  

Helps measure course duration.

---

EXTRACT — Pull Out Part of a Date

EXTRACT returns a specific part of a date.

EXTRACT(part FROM date)
  

Common parts:

• YEAR
• MONTH
• DAY

Example 5: Extract the year from a date

SELECT EXTRACT(YEAR FROM start_date) AS StartYear
FROM courses;
  

Useful for grouping or filtering by year.

---

Example 6: Extract month from enrollment date

SELECT EXTRACT(MONTH FROM enrollment_date) AS EnrollMonth
FROM enrollments;
  

Helps identify seasonal patterns.

---

Why Date Functions Matter

• Real data almost always includes dates
• You need to calculate time differences
• You often add or subtract time
• You frequently group by year or month

Beginners quickly learn that date functions make time-based analysis possible.

---

Date Functions Summary

Function	Purpose	Beginner Explanation
DATEADD	Add or subtract time	“Move the date forward or backward.”
DATEDIFF	Find difference between two dates	“How many days/months/years between these dates?”
EXTRACT	Pull out part of a date	“Give me the year, month, or day.”

---

Practice Tasks (Your Turn!)

Task 1

For each course, display the course name and calculate a date that is 30 days from today.

Task 2

For each enrollment, calculate how many days have passed since a given enrollment date.

Task 3

Extract the year from each student’s birth date.