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,TIMESTAMPtypes - Use functions like
NOW(),DATE_ADD(), andDATEDIFF()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 :
