🕒 SQL Dates – Handle, Filter, and Format Date/Time in SQL

🧲 Introduction – Why Master SQL Dates?

Working with dates in SQL is essential for time-based filtering, reporting, scheduling, and data analysis. SQL supports robust date and time handling across various functions and formats.

🎯 In this guide, you’ll learn:

• Common SQL date types and functions
• How to filter, format, and manipulate dates
• Cross-platform differences and best practices

---

📅 1. SQL Date/Time Data Types

Type	Description
DATE	Calendar date (YYYY-MM-DD)
TIME	Time of day (HH:MM:SS)
DATETIME	Date and time combined (MySQL)
TIMESTAMP	Date/time with timezone or epoch info
INTERVAL	Duration between two times (PostgreSQL)

---

🛠️ 2. Insert and Select Dates

INSERT INTO events (event_date) VALUES ('2025-12-25');
SELECT * FROM events WHERE event_date = '2025-12-25';

✅ Always use YYYY-MM-DD format for compatibility.

---

⏱️ 3. Common Date Functions

Function	Description
NOW()	Current date and time
CURRENT_DATE	Today’s date
DATE_ADD()	Add interval (MySQL)
DATE_SUB()	Subtract interval (MySQL)
DATE_PART()	Extract year/month/day (PostgreSQL)
EXTRACT()	Extract field from date/time
DATEDIFF()	Days between two dates (MySQL/SQL Server)
AGE()	Difference between dates (PostgreSQL)

---

🔍 4. Filtering with Dates

-- Find events after today
SELECT * FROM events WHERE event_date > CURRENT_DATE;

-- Events within 7 days
SELECT * FROM events
WHERE event_date BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY);

✅ Useful for scheduling, reminders, time-based alerts.

---

📐 5. Formatting Dates

DBMS	Function Example
MySQL	DATE_FORMAT(date_col, '%Y-%m-%d')
PostgreSQL	TO_CHAR(date_col, 'YYYY-MM-DD')
SQL Server	FORMAT(date_col, 'yyyy-MM-dd')

✅ Format dates for reports, exports, or readable output.

---

📘 Best Practices

✅ Recommended	❌ Avoid This
Use YYYY-MM-DD for date literals	Using ambiguous formats (MM/DD/YY)
Use built-in functions for consistency	Manually calculating intervals
Use indexed datetime columns for filtering	Using functions on the column side of WHERE

---

📌 Summary – Recap & Next Steps

Date and time functions in SQL allow powerful temporal analysis across datasets. Whether you’re filtering by timeframes, comparing durations, or formatting output, date handling is a core skill.

🔍 Key Takeaways:

• Understand DATE, TIME, DATETIME, TIMESTAMP types
• Use functions like NOW(), DATE_ADD(), and DATEDIFF() for manipulation
• Format and filter time ranges with built-in utilities

⚙️ Real-World Relevance:
Used in scheduling apps, billing cycles, attendance systems, time series analytics, and log processing.

---

❓ FAQ – SQL Dates

❓ How do I get the current date in SQL?

✅ Use CURRENT_DATE or NOW() depending on whether you want date or datetime.

❓ How do I calculate the number of days between two dates?

✅ Use DATEDIFF() in MySQL/SQL Server or AGE() in PostgreSQL.

❓ How do I extract year or month from a date?

✅ Use EXTRACT(YEAR FROM date_column) or DATE_PART('month', date_column).

❓ Can I compare just the date part of a timestamp?

✅ Yes. Use CAST(timestamp_col AS DATE) or extract the ::DATE portion.

---

Share Now :