SQL Working With Dates
Here is a clear, practical, and complete guide to working with dates in SQL, including the most important functions and examples for MySQL, PostgreSQL, SQL Server, and Oracle.
✅ Date Types in SQL
Most databases use several date/time data types:
| DB Engine | Date Types |
|---|---|
| MySQL | DATE, TIME, DATETIME, TIMESTAMP |
| PostgreSQL | DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL |
| SQL Server | DATE, TIME, DATETIME, DATETIME2 |
| Oracle | DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE |
🔹 1. Getting the Current Date / Time
MySQL
PostgreSQL
SQL Server
Oracle
🔹 2. Extracting Parts of a Date
Universal (ANSI SQL)
MySQL
PostgreSQL
SQL Server
🔹 3. Formatting Dates
MySQL
PostgreSQL
SQL Server
Oracle
🔹 4. Date Arithmetic
Adding days, months, years
MySQL
PostgreSQL
SQL Server
Oracle
🔹 5. Calculating Date Differences
MySQL
PostgreSQL
SQL Server
Oracle
🔹 6. Filtering by Date
Ranges
✔ Always use ISO format: YYYY-MM-DD
🔹 7. Working With Time Zones
PostgreSQL (best TZ support)
MySQL
SQL Server
🔹 8. Converting Strings to Dates
MySQL
PostgreSQL
SQL Server
Oracle
🔥 Real-World Examples
Get orders placed today
Get last 7 days of orders
MySQL/PostgreSQL:
SQL Server:
Group sales by month
(MySQL uses DATE_FORMAT and SQL Server uses FORMAT or EOMONTH.)
🏎 Best Practices for Working With Dates
✔ Always store dates in UTC
✔ Convert to local time in the application layer
✔ Use TIMESTAMP/TIMESTAMPTZ instead of DATETIME where possible
✔ Avoid storing dates as strings
✔ Use ISO format (YYYY-MM-DD) for filters
✔ Index date columns for faster range queries
✔ Beware of timezone shifts (DST transitions)
