22. Date Functions -- Working with Dates and Times

Level: Beginner

ℹ️ What You'll Learn

• GETDATE, SYSDATETIME -- current date/time
• DATEADD -- add/subtract intervals
• DATEDIFF -- calculate difference
• DAY, MONTH, YEAR -- extract parts
• EOMONTH -- end of month
• DATEFROMPARTS -- build date
• Date formatting
• Common date mistakes
• Best practices

Databases store dates. APIs need calculations: age, days overdue, academic year. Date functions solve timing queries. This article teaches date arithmetic.

Why Date Functions Matter for Backend Developers

APIs need date logic:

Frontend asks: "Show students older than 18, with overdue fees"
    v
Backend needs:
- Calculate age from DateOfBirth (DATEDIFF)
- Check FeeAccount DueDate vs today (GETDATE)
    v
Date functions do arithmetic in SQL
    v
Filter by age and due date

Date calculations in database, not backend.

Date Function Reference

GETDATE()                           -> Current date and time
SYSDATETIME()                       -> Current date/time (precise)
DATEADD(unit, interval, date)       -> Add/subtract time
DATEDIFF(unit, start, end)          -> Difference between dates
DAY(date)                           -> Day of month (1-31)
MONTH(date)                         -> Month (1-12)
YEAR(date)                          -> Year
EOMONTH(date)                       -> Last day of month
DATEFROMPARTS(year, month, day)     -> Build date
EOMONTH(date, months)               -> Add months, get end of month

Example 1: Calculate Student Age

Get age in years:

SELECT 
    Name,
    DateOfBirth,
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS AgeInYears
FROM Student;

Result (ages):

Name         | DateOfBirth | AgeInYears
Ravi Kumar   | 2008-05-15  | 16
Priya Sharma | 2007-11-20  | 18
Arjun Reddy  | 2008-02-03  | 16

DATEDIFF(YEAR, start, end): Years between birthday and today.

More precise (accounting for birthday not passed yet):

SELECT 
    Name,
    DateOfBirth,
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) - 
    CASE 
        WHEN MONTH(DateOfBirth) > MONTH(GETDATE())
             OR (MONTH(DateOfBirth) = MONTH(GETDATE()) 
                 AND DAY(DateOfBirth) > DAY(GETDATE()))
        THEN 1
        ELSE 0
    END AS ExactAge
FROM Student;

Checks if birthday passed this year.

Example 2: Days Since Exam Date

Calculate days elapsed:

SELECT 
    e.ExamName,
    e.ExamDate,
    DATEDIFF(DAY, e.ExamDate, GETDATE()) AS DaysSinceExam
FROM Exam e
WHERE e.ExamDate <= GETDATE();

Result (days passed):

ExamName  | ExamDate   | DaysSinceExam
Final     | 2024-05-10 | 45
Midterm   | 2024-03-20 | 86

DATEDIFF(DAY, past, today): Days elapsed.

Example 3: Fee Overdue (Days Past Due Date)

Check overdue fees:

SELECT 
    s.Name AS Student,
    f.DueDate,
    GETDATE() AS Today,
    DATEDIFF(DAY, f.DueDate, GETDATE()) AS DaysOverdue,
    CASE 
        WHEN DATEDIFF(DAY, f.DueDate, GETDATE()) > 0 THEN 'Overdue'
        WHEN DATEDIFF(DAY, f.DueDate, GETDATE()) = 0 THEN 'Due Today'
        ELSE 'Pending'
    END AS Status
FROM FeeAccount f
INNER JOIN Student s ON f.StudentId = s.StudentId
ORDER BY DaysOverdue DESC;

Result (overdue status):

Student      | DueDate    | Today      | DaysOverdue | Status
Ravi Kumar   | 2024-04-30 | 2024-05-24 | 24          | Overdue
Priya Sharma | 2024-05-30 | 2024-05-24 | -6          | Pending

Negative days = future due date (not yet due).

Example 4: Add Days/Months to Date

Calculate next fee due date (3 months from now):

SELECT 
    f.FeeAccountId,
    f.DueDate,
    DATEADD(MONTH, 3, f.DueDate) AS NextDueDate,
    DATEADD(DAY, -1, EOMONTH(GETDATE(), 3)) AS MonthEndAfter3Months
FROM FeeAccount f;

Result (future dates):

FeeAccountId | DueDate    | NextDueDate | MonthEndAfter3Months
1            | 2024-05-31 | 2024-08-31  | 2024-08-31
2            | 2024-06-15 | 2024-09-15  | 2024-08-31

DATEADD(MONTH, 3, date): Add 3 months. EOMONTH(date, 3): End of month 3 months from date.

Example 5: Academic Year Calculation

Get academic year (starts April 1):

SELECT 
    CASE 
        WHEN MONTH(GETDATE()) >= 4 
        THEN CONCAT(YEAR(GETDATE()), '-', YEAR(GETDATE()) + 1)
        ELSE CONCAT(YEAR(GETDATE()) - 1, '-', YEAR(GETDATE()))
    END AS CurrentAcademicYear;

Result (academic year):

CurrentAcademicYear
2024-2025

If month >= 4 (April onward): Current year-next year. Else: Previous year-current year.

Example 6: Attendance Report (This Month)

Get attendance for current month:

SELECT 
    s.Name AS Student,
    COUNT(a.AttendanceId) AS ClassesHeld,
    SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) AS Present,
    CAST(
        SUM(CASE WHEN a.IsPresent = 1 THEN 1 ELSE 0 END) * 100.0 / 
        COUNT(a.AttendanceId) AS DECIMAL(5,2)
    ) AS AttendancePercentage
FROM Student s
LEFT JOIN Attendance a ON s.StudentId = a.StudentId
WHERE MONTH(a.Date) = MONTH(GETDATE())
    AND YEAR(a.Date) = YEAR(GETDATE())
GROUP BY s.StudentId, s.Name
ORDER BY AttendancePercentage DESC;

Result (monthly attendance):

Student     | ClassesHeld | Present | AttendancePercentage
Ravi Kumar  | 20          | 19      | 95.00
Priya Sharma| 20          | 18      | 90.00

MONTH and YEAR extract parts. Filter current month/year.

Example 7: Date Range Query

Get all exams scheduled for next 30 days:

SELECT 
    e.ExamName,
    e.ExamDate,
    DATEDIFF(DAY, GETDATE(), e.ExamDate) AS DaysUntilExam
FROM Exam e
WHERE e.ExamDate BETWEEN GETDATE() AND DATEADD(DAY, 30, GETDATE())
ORDER BY e.ExamDate;

Result (upcoming exams):

ExamName | ExamDate   | DaysUntilExam
Final    | 2024-06-15 | 22
Practical| 2024-06-20 | 27

BETWEEN GETDATE() AND DATEADD(DAY, 30, GETDATE()): Next 30 days.

---

Common Date Mistakes

Mistake 1: Comparing dates with time component

Wrong:

WHERE ExamDate = '2024-05-15'
-- Exam stored as 2024-05-15 10:30:00
-- Condition false (times don't match)

Result: No results.

Fix: Use CAST or CONVERT:

WHERE CAST(ExamDate AS DATE) = '2024-05-15';
-- Or:
WHERE ExamDate >= '2024-05-15' AND ExamDate < '2024-05-16';

Mistake 2: Wrong DATEDIFF order (reversed)

Wrong:

SELECT DATEDIFF(DAY, GETDATE(), DateOfBirth) FROM Student;
-- Returns negative age

Result: Wrong sign.

Fix: Correct order (earlier first):

SELECT DATEDIFF(DAY, DateOfBirth, GETDATE()) FROM Student;
-- Positive age

Mistake 3: Using GETDATE() in WHERE on indexed column

Wrong:

WHERE ExamDate > GETDATE()
-- GETDATE() evaluated per row, can't use index

Result: Table scan.

Fix: Assign to variable first:

DECLARE @Today DATE = CAST(GETDATE() AS DATE);
WHERE ExamDate > @Today;
-- Or:
WHERE ExamDate > CAST(GETDATE() AS DATE);

Mistake 4: Month arithmetic without handling edge cases

Wrong:

SELECT DATEADD(MONTH, 1, '2024-01-31') AS NextMonth;
-- Returns 2024-02-29 (not 2024-03-31)
-- Last day of month not preserved

Result: Wrong date (unexpected month-end).

Fix: Use EOMONTH:

SELECT DATEADD(DAY, -1, EOMONTH(DATEADD(MONTH, 1, '2024-01-31'))) AS NextMonthEnd;
-- Returns 2024-02-29 (correct end of February)

Mistake 5: Timezone issues with stored dates

Wrong:

INSERT INTO Exam (ExamDate) VALUES (GETDATE());
-- Different server timezone than client
-- Data stored in UTC, client sees local time (mismatch)

Result: Timezone confusion.

Fix: Use UTC consistently:

INSERT INTO Exam (ExamDate) VALUES (GETUTCDATE());
-- Or document timezone in schema

Store times in UTC. Convert to local on display.

---

Best Practices for Date Functions

1. Store dates in DATE or DATETIME2 -- Not VARCHAR
2. Use GETDATE() for current time -- Consistent across query
3. CAST to DATE for comparisons -- Avoid time component issues
4. DATEDIFF order: older first -- Returns positive difference
5. Use EOMONTH for month-end -- Handles variable month lengths
6. Document timezone policy -- UTC vs local
7. Test with edge cases -- Month boundaries, leap years

---

🎯 Q1: How do I calculate age from DateOfBirth?

Simple (year difference only):

SELECT DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS Age
FROM Student;
-- Age 18 even if birthday hasn't passed this year

Precise (accounting for birthday):

SELECT 
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) -
    CASE 
        WHEN MONTH(DateOfBirth) > MONTH(GETDATE())
             OR (MONTH(DateOfBirth) = MONTH(GETDATE()) 
                 AND DAY(DateOfBirth) > DAY(GETDATE()))
        THEN 1
        ELSE 0
    END AS ExactAge

Check if birthday passed this year.

🎯 Q2: What's the difference between DATE and DATETIME data types?

DATE: Only date (year-month-day).

2024-05-15

DATETIME: Date and time (to millisecond).

2024-05-15 14:30:45.123

DATETIME2: More precise (nanosecond).

2024-05-15 14:30:45.1234567

Use DATE for dates. DATETIME for timestamps.

🎯 Q3: How do I get the last day of current month?

SELECT EOMONTH(GETDATE());
-- Returns 2024-05-31

SELECT EOMONTH(GETDATE(), -1);
-- Returns last day of previous month (2024-04-30)

SELECT EOMONTH(GETDATE(), 3);
-- Returns last day 3 months from now (2024-08-31)

EOMONTH: End of month. Takes offset parameter.

🎯 Q4: What's the difference between DATEDIFF and DATEADD?

DATEDIFF: Calculate difference.

SELECT DATEDIFF(DAY, '2024-05-01', '2024-05-15');
-- Returns 14 (days between)

DATEADD: Add/subtract time.

SELECT DATEADD(DAY, 14, '2024-05-01');
-- Returns 2024-05-15 (add 14 days)

DATEDIFF: Subtraction. DATEADD: Addition.

🎯 Q5: How do I query records between two dates?

Using BETWEEN:

WHERE ExamDate BETWEEN '2024-05-01' AND '2024-05-31';

Using DATEADD for relative ranges:

WHERE ExamDate >= GETDATE() 
  AND ExamDate <= DATEADD(DAY, 30, GETDATE());

Query current month:

WHERE MONTH(ExamDate) = MONTH(GETDATE())
  AND YEAR(ExamDate) = YEAR(GETDATE());

BETWEEN clearest. DATEADD for relative ranges.

🎯 Q6: What happens when I add months to Jan 31?

SELECT DATEADD(MONTH, 1, '2024-01-31');
-- Returns 2024-02-29 (last day of February)
-- Not 2024-03-31

SQL aligns to last valid day of target month.

If you want end of next month:

SELECT DATEADD(DAY, -1, EOMONTH(DATEADD(MONTH, 1, '2024-01-31')));
-- Returns 2024-02-29 (explicit)

Use EOMONTH for month-end arithmetic.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Date functions for calculations in SQL Server. Try these prompts:

• "Show how to calculate student age from DateOfBirth"
• "How do I find all exams in the next 30 days?"
• "What's the difference between DATEADD and DATEDIFF?"
• "How do I generate an academic year (April-March) report?"
• "Quiz me: ask 5 questions about date functions, EOMONTH, age calculation"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

23. Math Functions & CASE Logic -- Calculations and Conditionals ->