SQL Date and Time Functions: A Practical Reference

Why Date Functions Matter in Analytics SQL

Dates are central to almost every analytical query — time series trends, date range filters, period-over-period comparisons, age calculations, and cohort assignments all require date operations. SQL date functions are inconsistently named across databases, which makes cross-platform SQL development frustrating.

This guide covers the essential date operations with examples for BigQuery, Snowflake, PostgreSQL, and Redshift.

Date Truncation

Truncation rounds a timestamp down to the nearest unit — day, week, month, quarter, year. Used for grouping events into time periods.

**BigQuery**: DATE_TRUNC(date_expr, granularity) or TIMESTAMP_TRUNC(timestamp_expr, granularity)

- DATE_TRUNC('2024-04-15', MONTH) returns 2024-04-01

- TIMESTAMP_TRUNC(timestamp_col, HOUR) returns timestamp rounded to the hour

**Snowflake**: DATE_TRUNC(granularity, date_expr) — note argument order reversed from BigQuery

- DATE_TRUNC('month', '2024-04-15') returns 2024-04-01

**PostgreSQL**: DATE_TRUNC(granularity, date_expr)

- DATE_TRUNC('month', '2024-04-15') returns 2024-04-01

**Redshift**: DATE_TRUNC(granularity, date_expr)

- DATE_TRUNC('month', '2024-04-15') returns 2024-04-01

Common granularity strings: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'

Note: 'week' typically truncates to Monday (ISO week start). For Sunday-start weeks, use 'week' in Snowflake/PostgreSQL (check locale) or add explicit DAYOFWEEK logic.

Date Extraction

Extracting parts of a date — year, month, day of week — for use in GROUP BY or calculations.

**BigQuery**: EXTRACT(part FROM date_expr)

- EXTRACT(YEAR FROM order_date) → 2024

- EXTRACT(DAYOFWEEK FROM order_date) → 1 (Sunday) to 7 (Saturday)

- BigQuery also supports: FORMAT_DATE('%A', order_date) for day name

**Snowflake**: EXTRACT(part FROM date) or specific functions: YEAR(date), MONTH(date), DAY(date), DAYOFWEEK(date), QUARTER(date), WEEKOFYEAR(date)

**PostgreSQL**: EXTRACT(part FROM date) or DATE_PART(part, date)

- EXTRACT(DOW FROM order_date) → 0 (Sunday) to 6 (Saturday)

- EXTRACT(ISODOW FROM order_date) → 1 (Monday) to 7 (Sunday)

**Redshift**: EXTRACT(part FROM date) or DATE_PART(part, date)

- DATEPART syntax also supported: DATEPART(quarter, order_date)

Date Arithmetic

Adding or subtracting intervals from dates.

**BigQuery**: DATE_ADD(date, INTERVAL n unit) / DATE_SUB(date, INTERVAL n unit)

- DATE_ADD('2024-01-15', INTERVAL 30 DAY) → 2024-02-14

- DATE_ADD(order_date, INTERVAL 1 MONTH) → same day in next month

**Snowflake**: DATEADD(unit, n, date)

- DATEADD('day', 30, '2024-01-15') → 2024-02-14

- DATEADD('month', -1, order_date) → same day in prior month

**PostgreSQL**: date + INTERVAL '30 days' or date - INTERVAL '1 month'

- '2024-01-15'::date + INTERVAL '30 days' → 2024-02-14

- CURRENT_DATE - INTERVAL '7 days'

**Redshift**: DATEADD(unit, n, date) — same as Snowflake syntax

- Also supports: date + INTEGER adds days directly

Date Difference

Calculating the number of days, months, or other units between two dates.

**BigQuery**: DATE_DIFF(end_date, start_date, unit)

- DATE_DIFF('2024-03-15', '2024-01-15', DAY) → 60

- DATE_DIFF('2024-03-15', '2024-01-15', MONTH) → 2

- TIMESTAMP_DIFF(ts1, ts2, SECOND) for timestamp differences

**Snowflake**: DATEDIFF(unit, start_date, end_date) — note start/end order opposite to BigQuery

- DATEDIFF('day', '2024-01-15', '2024-03-15') → 60

**PostgreSQL**: date2 - date1 returns integer days

- '2024-03-15'::date - '2024-01-15'::date → 60

- For months: EXTRACT(year FROM AGE(end_date, start_date)) * 12 + EXTRACT(month FROM AGE(end_date, start_date))

**Redshift**: DATEDIFF(unit, start_date, end_date) — same as Snowflake

Current Date and Timestamp

**BigQuery**: CURRENT_DATE(), CURRENT_TIMESTAMP(), CURRENT_DATETIME()

**Snowflake**: CURRENT_DATE(), CURRENT_TIMESTAMP(), GETDATE()

**PostgreSQL**: CURRENT_DATE, CURRENT_TIMESTAMP, NOW()

**Redshift**: CURRENT_DATE, GETDATE(), SYSDATE

Date Formatting and Parsing

Converting dates to strings and strings to dates.

**BigQuery**:

- FORMAT_DATE('%Y-%m-%d', order_date) → '2024-04-15'

- FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', ts) → formatted timestamp string

- PARSE_DATE('%Y-%m-%d', '2024-04-15') → DATE

- PARSE_TIMESTAMP('%Y-%m-%dT%H:%M:%S', '2024-04-15T14:30:00') → TIMESTAMP

**Snowflake**:

- TO_CHAR(order_date, 'YYYY-MM-DD') → '2024-04-15'

- TO_CHAR(timestamp_col, 'YYYY-MM-DD HH24:MI:SS')

- TO_DATE('2024-04-15', 'YYYY-MM-DD') → DATE

- TRY_TO_DATE for parsing without error on bad input

**PostgreSQL**:

- TO_CHAR(order_date, 'YYYY-MM-DD') → '2024-04-15'

- TO_DATE('2024-04-15', 'YYYY-MM-DD') → DATE

- TO_TIMESTAMP('2024-04-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') → TIMESTAMP

**Redshift**: Same as PostgreSQL syntax (TO_CHAR, TO_DATE, TO_TIMESTAMP)

Timezone Handling

**BigQuery**:

- TIMESTAMP type stores UTC; DATETIME has no timezone

- CONVERT_TIMEZONE(source_tz, target_tz, timestamp) via AT TIME ZONE: ts AT TIME ZONE 'America/New_York'

- Recommendation: store all timestamps in UTC, convert to user timezone at query or display time

**Snowflake**:

- TIMESTAMP_NTZ: no timezone (wall clock time)

- TIMESTAMP_LTZ: local timezone stored and applied

- TIMESTAMP_TZ: timezone stored with the value

- CONVERT_TIMEZONE('UTC', 'America/New_York', ts) for conversion

**PostgreSQL**:

- timestamp WITHOUT TIME ZONE: no timezone

- timestamp WITH TIME ZONE (timestamptz): stored as UTC, displayed in session timezone

- AT TIME ZONE for conversion: ts AT TIME ZONE 'America/New_York'

Common Date Patterns

**First and last day of month**:

BigQuery: DATE_TRUNC(date_col, MONTH) for first day; DATE_ADD(DATE_TRUNC(DATE_ADD(date_col, INTERVAL 1 MONTH), MONTH), INTERVAL -1 DAY) for last day

PostgreSQL/Redshift: DATE_TRUNC('month', date_col) for first; DATE_TRUNC('month', date_col) + INTERVAL '1 month' - INTERVAL '1 day' for last

Snowflake: DATE_TRUNC('month', date_col) for first; LAST_DAY(date_col) for last

**Age calculation**:

BigQuery: DATE_DIFF(CURRENT_DATE(), birthdate, YEAR) — note: may be off by 1 before birthday in current year

PostgreSQL: EXTRACT(YEAR FROM AGE(birthdate)) — AGE accounts for birthday correctly

Snowflake: DATEDIFF('year', birthdate, CURRENT_DATE())

**Business days**: No native business-day function in most SQL databases. Compute by counting weekdays using DAYOFWEEK and subtracting weekends, or by joining to a calendar table with a business_day flag.

Our data architecture practice builds SQL transformations and dbt models for enterprise analytics — contact us to discuss your data engineering requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.