Written by the

SQL: DATE AND TIME FUNCTIONS

Master temporal database queries: current timestamps, ISO formatting, date part extraction, interval arithmetic with DATEADD, date difference calculations with DATEDIFF, truncation bucketing, timezone mechanics, and age formulas.

Getting the Current Date and Time: NOW(), CURRENT_DATE, GETDATE()

Relational databases provide built-in functions to retrieve the current system date and time during query execution. While ANSI SQL standardizes keywords like CURRENT_DATE and CURRENT_TIMESTAMP, individual database systems offer vendor-specific functions.

Function / KeywordSupported EnginesOutput TypeExample Output
CURRENT_DATEANSI Standard, PostgreSQL, SQLite, MySQLDate only2026-10-10
CURRENT_TIMESTAMPANSI Standard, PostgreSQL, SQLite, MySQL, SQL ServerDate and time2026-10-10 14:30:00
NOW()PostgreSQL, MySQLTimestamp with timezone2026-10-10 14:30:00+00
GETDATE()Microsoft SQL ServerDatetime2026-10-10 14:30:00.123
SYSDATEOracle DatabaseDate with time10-OCT-26

Retrieving System Date and Time

SQL

Query real-time system timestamps and evaluate ISO output representations

Transaction-Stable vs Wall-Clock Time

  • Transaction Constancy:In PostgreSQL and MySQL, NOW() returns the start timestamp of the current transaction and does not drift during long-running operations.
  • Clock Drifting:Functions like CLOCK_TIMESTAMP() (Postgres) or SYSDATETIME() (SQL Server) return actual wall-clock time as each row is evaluated.

DATE Format and Formatting Dates

The international standard for dates in relational databases is ISO-8601: YYYY-MM-DD (e.g. 2026-10-10). Storing dates in ISO-8601 format ensures natural chronological ordering under standard alphabetical sorting and eliminates regional date ambiguity (such as day-first versus month-first conventions).

When generating user-facing reports, SQL provides formatting functions:

  • SQLite: strftime(format_string, date_column)
  • PostgreSQL: TO_CHAR(date_column, 'YYYY-MM-DD')
  • MySQL: DATE_FORMAT(date_column, '%Y-%m-%d')
  • SQL Server: FORMAT(date_column, 'yyyy-MM-dd')

Formatting Dates with strftime

SQL

Transform ISO-8601 dates into regional display formats

Common Format Specifiers

  • %Y:Four-digit year (e.g. 2026).
  • %m:Two-digit month (01 through 12).
  • %d:Two-digit day of month (01 through 31).
  • %H / %M / %S:Two-digit hours (24-hour clock), minutes, and seconds.

Extracting Parts: YEAR, MONTH, DAY, EXTRACT

When analyzing trends or aggregating metrics, you often need to isolate individual components such as the year, quarter, month, or day of week:

  • ANSI SQL Standard: EXTRACT(part FROM timestamp) (supported in PostgreSQL, MySQL, Oracle).
  • Helper Functions: YEAR(col), MONTH(col), DAY(col) (supported in MySQL and SQL Server).
  • SQLite: CAST(strftime('%Y', col) AS INT) or CAST(strftime('%m', col) AS INT).

Isolating Date Components

SQL

Deconstruct full date stamps into numeric year, month, and day fields

Syntax Matrix across Database Engines

  • PostgreSQL:EXTRACT(YEAR FROM hire_date) or date_part('year', hire_date).
  • MySQL:YEAR(hire_date), MONTH(hire_date), DAY(hire_date).
  • SQL Server:DATEPART(year, hire_date) or YEAR(hire_date).
  • SQLite:strftime('%Y', hire_date) returns text; wrap in CAST(... AS INT) for numeric comparison.

DATEADD and DATE_ADD (Adding Days, Months, Years)

Date arithmetic adds or subtracts temporal intervals (days, months, years) to calculate deadlines, expiration periods, warranty windows, and reminders.

EngineSyntax for Adding 30 DaysSyntax for Subtracting 1 Year
SQLitedate(order_date, '+30 days')date(order_date, '-1 year')
MySQLDATE_ADD(order_date, INTERVAL 30 DAY)DATE_SUB(order_date, INTERVAL 1 YEAR)
PostgreSQLorder_date + INTERVAL '30 days'order_date - INTERVAL '1 year'
SQL ServerDATEADD(day, 30, order_date)DATEADD(year, -1, order_date)

Interval Arithmetic with Date Modifiers

SQL

Project forward and backward deadlines using interval arithmetic

End of Month Rollover Rules

  • Month Length Discrepancies:Adding 1 month to January 31st behaves differently across engines: SQLite clamps to February 28/29, while some systems require explicit end-of-month handling.

DATEDIFF (Difference Between Two Dates)

Calculating the elapsed duration between two dates (such as days to fulfill an order, employee tenure, or account age) is achieved using difference operators:

  • SQL Server: DATEDIFF(day, start_date, end_date) (accepts units like day, month, year).
  • MySQL: DATEDIFF(end_date, start_date) (returns integer days).
  • PostgreSQL: end_date - start_date (returns integer days) or AGE(end_date, start_date).
  • SQLite: julianday(end_date) - julianday(start_date) (returns fractional days).

Calculating Elapsed Date Differences

SQL

Compute duration and tenure between two dates using Julian day arithmetic

Argument Ordering Warning

  • Parameter Order Flips:In MySQL, DATEDIFF(end, start) takes end first; in SQL Server, DATEDIFF(unit, start, end) takes start first.
  • Boundary Crossing:SQL Server DATEDIFF counts boundary crossings: DATEDIFF(year, '2025-12-31', '2026-01-01') returns 1 even though only 1 day has passed.

DATE_TRUNC and Grouping by Month or Year

When aggregating transactional data into monthly or annual cohorts, grouping by individual timestamps causes fragmented single-row groups. Date truncation resets all units below a specified precision back to their baseline (for example, truncating 2026-10-14 15:42:10 to month produces 2026-10-01 00:00:00).

  • PostgreSQL: DATE_TRUNC('month', timestamp_column)
  • SQL Server (2022+): DATETRUNC(month, timestamp_column)
  • SQLite: date(timestamp_column, 'start of month') or strftime('%Y-%m-01', timestamp_column)

Temporal Cohort Grouping with Date Truncation

SQL

Aggregate metrics into monthly cohorts by resetting dates to the start of each month

DATE_TRUNC vs strftime String Grouping

  • Preserving Date Type:DATE_TRUNC preserves the underlying DATE/TIMESTAMP data type, allowing subsequent date arithmetic.
  • String Formatting:Grouping by strftime('%Y-%m', date) converts dates to strings; valid for display but drops day/time arithmetic capability.

Converting String to Date

User inputs, CSV files, and external APIs often provide dates as raw strings with regional formatting (such as '14/03/2026' or 'March 14, 2026'). To perform chronological sorting or range filtering, strings must be parsed into native date types.

Database EngineParsing FunctionExample Usage
PostgreSQLTO_DATE(str, format)TO_DATE('14-10-2026', 'DD-MM-YYYY')
MySQLSTR_TO_DATE(str, format)STR_TO_DATE('14/10/2026', '%d/%m/%Y')
SQL ServerCONVERT(DATE, str, style)CONVERT(DATE, '20261014', 112)
SQLitedate(str)date('2026-10-14')

Converting Text Strings to Date Data Types

SQL

Normalize text representations into valid ISO date structures

Date Casting Best Practice

  • Always Use ISO-8601:Store strings in YYYY-MM-DD format to ensure every database parses dates without requiring custom locale format strings.

Comparing Dates and Date Ranges

Filtering data across a calendar window (e.g. all orders in Q1 2026) can be written using comparison operators (>=, <=) or the BETWEEN operator.

A common performance mistake is wrapping the date column in an extraction function (e.g. WHERE strftime('%Y', order_date) = '2026'). This makes the query non-SARGable, forcing the engine to scan every row in the table rather than executing an index seek.

SARGable Date Range Filtering

SQL

Filter date ranges using index-friendly inequality boundaries

The SARGability Rule for Dates

  • Bad (Non-SARGable):WHERE YEAR(order_date) = 2026 forces full table scans because the engine must run the function on every single row.
  • Good (SARGable):WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' allows direct B-Tree index seeks.

Working with Time Zones

Distributed systems operate across multiple regions, making timezone handling essential to prevent discrepancies:

  • Golden Architecture Rule: Always store timestamps in UTC (Coordinated Universal Time) at the database layer. Convert to local user timezones only in presentation queries or frontend applications.
  • PostgreSQL: timestamp_col AT TIME ZONE 'America/New_York'
  • MySQL: CONVERT_TZ(timestamp_col, '+00:00', '-05:00')
  • SQLite: datetime(col, 'localtime') or datetime(col, 'utc')

Time Zone Offsets and Conversions

SQL

Convert UTC timestamps into regional local offsets

Daylight Saving Time (DST) Caveat

  • Named Zones vs Fixed Offsets:Fixed offsets (+05:00) do not adjust for daylight saving changes; modern engines (Postgres/SQL Server) support IANA zone names ('America/New_York') that automatically adjust.

Calculating Age from Date of Birth

Simply subtracting the birth year from the current year (current_year - birth_year) produces inaccurate ages because it fails to account for whether the individual has had their birthday in the current calendar year.

The universally accurate integer math formula converts dates to YYYYMMDD integers, subtracts birthdate from current date, and divides by 10,000:

CAST((strftime('%Y%m%d', 'now') - strftime('%Y%m%d', dob)) / 10000 AS INT)

Accurate Age Calculation Formula

SQL

Compute verified biological ages accounting for birth month and day boundaries

Why the Integer Division Method Works

  • The Logic:If today is 20261010 and birthdate is 20001115 (birthday has not happened yet), 20261010 - 20001115 = 259895. Dividing by 10000 yields exactly 25 years old.
  • PostgreSQL Native:In PostgreSQL, you can simply call EXTRACT(YEAR FROM AGE(CURRENT_DATE, dob)).

Knowledge Check: SQL Date & Time Functions

1. Which standard ANSI SQL keyword returns the current date without a time component?

2. What is the standard ISO-8601 date format used universally in SQL systems?

3. How do you add 14 days to a date portably in SQLite?

4. What does the DATE_TRUNC function do in relational database queries?

5. Why should date comparisons avoid wrapping column names in functions like YEAR(col)?

6. How is exact age in years calculated in SQLite from a birthdate?

PreviousNext