CURRENT_DATE returns a DATE value for the applicable time zone. It is not a character string with a guaranteed YYYY-MM-DD display. Use an explicit conversion for text output and define whether a reporting window includes today, excludes today or represents a rolling timestamp interval.
Lab check: 24 October 2026 · Teradata 20.00.28.81 · BTEQ 20.00.00.11 · BTET. DATE type, date arithmetic and the five-row boundary example below were executed. The displayed date changes with execution time and timezone. Download the SQL and observed results.
Check the type and format explicitly
SELECT TYPE(CURRENT_DATE) AS result_type,
(CURRENT_DATE - (CURRENT_DATE - 7)) AS seven_days,
TO_CHAR(CURRENT_DATE,'YYYY-MM-DD') AS explicit_text;Observed on the lab run: DATE, 7 and 2026-10-24. Client display settings can format a DATE differently; TO_CHAR explicitly produces text. Session timezone settings and an explicit AT clause can affect the calendar date near midnight. Define the business timezone before comparing systems.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookFilter the previous seven completed calendar days
SELECT *
FROM your_table
WHERE event_date >= CURRENT_DATE - 7
AND event_date < CURRENT_DATE;This template assumes a DATE column and excludes today and future dates. For seven calendar dates including today, use event_date >= CURRENT_DATE - 6 AND event_date < CURRENT_DATE + 1. A rolling 168-hour window is a different requirement: use timestamp boundaries in a defined timezone rather than substituting a DATE predicate.
Boundary test: why the upper bound matters
We inserted five dates into a session-local volatile table: today minus 8 days, minus 7 days, minus 1 day, today, and tomorrow. The previous-seven-completed-days predicate returned 2 rows. A lower-bound-only predicate (event_date >= CURRENT_DATE - 7) returned 4 rows, including today and tomorrow. The downloadable script creates and drops only this test table.
Date differences and month extraction
SELECT CURRENT_DATE - event_date AS age_in_days
FROM your_table;
SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS current_month;These templates illustrate DATE arithmetic and extraction. If the source is a TIMESTAMP, decide whether you need calendar dates or elapsed time before converting it. NULL input and future-dated rows require an explicit business rule.
Why two sessions can disagree about today
Check their timezone settings, the instant at which each request ran, and any explicit AT clause. A formatting difference alone does not mean the underlying date differs. The lab check above did not test every timezone or a daylight-saving transition.
Reference: Teradata AT LOCAL and AT TIME ZONE rules. See also date formatting and date calculations.
Should “last seven days” include today?
Choose the reporting rule first. Seven completed calendar days excludes today; seven calendar dates including today starts at CURRENT_DATE minus 6. Neither automatically means the last 168 hours. Keep the upper bound explicit so future-dated records cannot enter a historical report.
Why did the result change at midnight?
CURRENT_DATE is evaluated for the applicable timezone and request time. For reproducible reports spanning multiple requests, capture an agreed as-of date and use it consistently. A timezone change or a later request crossing midnight can legitimately move the boundary.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book