SQL Working With Dates

SQL Tutorial

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 EngineDate Types
MySQLDATE, TIME, DATETIME, TIMESTAMP
PostgreSQLDATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL
SQL ServerDATE, TIME, DATETIME, DATETIME2
OracleDATE, 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)

You may also like...