SQL DAY

What is the SQL DAY keyword?

DAY is a date part keyword that represents a 24-hour period and is used in interval literals and date-time functions such as EXTRACT, DATEADD, and DATEDIFF.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DAY:

SQL DAY Full Explanation

DAY identifies the day component of a date or timestamp value. SQL engines expose it in two main contexts:1. Interval literals – INTERVAL 'n' DAY adds or subtracts whole days.2. Date-time functions – EXTRACT(DAY FROM ts) returns the numeric day of the month; DATEADD(DAY, n, date) shifts a date forward or backward by n days; DATEDIFF(DAY, date1, date2) calculates the number of days between two dates.Standard SQL also allows specifying DAY with higher-precision fields (DAY TO SECOND) to create complex intervals. Because DAY is language-reserved, it cannot be used as an unquoted column or table name without delimiters. Most engines treat DAY as case-insensitive.Caveats:- Some dialects (SQLite, MySQL) use alternative functions instead of DATEADD/DATEDIFF.- INTERVAL syntax varies: PostgreSQL follows the SQL standard, while SQL Server and MySQL require keywords like DAY following an interval expression or DATEADD.- When extracting DAY, remember that the result is the calendar day of month (1-31), not the day of week.

SQL DAY Syntax

-- Standard interval literal
INTERVAL '7' DAY

-- Extracting the day of month
EXTRACT(DAY FROM some_timestamp)

-- Adding days (T-SQL style)
DATEADD(DAY, 3, some_date)

-- Difference in days (T-SQL style)
DATEDIFF(DAY, start_date, end_date)

SQL DAY Parameters

Example Queries Using SQL DAY

-- 1. Add 7 days to an order date
SELECT order_id,
       order_date,
       order_date + INTERVAL '7' DAY AS expected_ship_date
FROM   orders;

-- 2. Get day of month for each signup
SELECT user_id,
       EXTRACT(DAY FROM signup_ts) AS signup_day
FROM   users;

-- 3. How many days since last login (SQL Server / Snowflake)
SELECT user_id,
       DATEDIFF(DAY, last_login, CURRENT_TIMESTAMP) AS days_inactive
FROM   users;

Expected Output Using SQL DAY

expected_ship_date shows a timestamp exactly 7 days after order_date.
signup_day returns integers 1-31 for each record.
days_inactive returns the integer count of days between last_login and now.

Use Cases with SQL DAY

Shift a date forward or backward by whole days.
Calculate the number of days between two events.
Extract the calendar day from a timestamp for reporting or bucketing.
Build interval arithmetic such as DAY TO SECOND ranges.

Common Mistakes with SQL DAY

Confusing EXTRACT(DAY) with day of week; use EXTRACT(DOW) or equivalent for weekday.
Forgetting that MySQL needs INTERVAL 7 DAY (no quotes) instead of standard literal.
Using DAY as an identifier without quoting ("DAY" or [DAY]).
Expecting DATEADD/DATEDIFF in engines that do not implement them (e.g., SQLite).

First Introduced In

SQL-92 (temporal data and INTERVAL types)

Frequently Asked Questions

What is the difference between DAY and DOW in EXTRACT?

DAY returns the calendar day 1-31. DOW (or equivalent) returns 0-6 for Sunday-Saturday.

How do I subtract days between two dates?

Use DATEDIFF(DAY, start_date, end_date) in T-SQL or end_date - start_date in PostgreSQL. Both yield the day count.

Can I combine DAY with other interval fields?

Yes. SQL supports composite intervals like INTERVAL '3 04:00' DAY TO HOUR, representing 3 days and 4 hours.

Is DAY case-sensitive?

No. SQL keywords are case-insensitive, so DAY, Day, and day behave the same.