SQL DATE

What is SQL DATE?

DATE stores a calendar date (year, month, day) with no time-of-day or timezone information.

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 DATE:

SQL DATE Full Explanation

DATE is a core ANSI-SQL data type for representing calendar dates independently of any clock time or time zone. A DATE value always contains three numeric parts—year, month, and day—formatted as YYYY-MM-DD in textual form. Internally, implementations often store DATE as an integer offset from a reference day, enabling efficient arithmetic (e.g., adding INTERVAL values).Key points:- Granularity is one day. Hours, minutes, seconds, and fractional seconds are not stored.- Time zone is never applied; DATE 2024-05-10 means the same calendar day everywhere.- Supported range varies by system (e.g., PostgreSQL 4713 BC to 5874897 AD; MySQL 1000-01-01 to 9999-12-31).- Standard SQL allows DATE literals using the keyword DATE followed by a string literal: DATE '2024-05-10'.- Conversions between DATE and TIMESTAMP types are allowed with CAST or implicit rules; the time portion defaults to 00:00:00 when casting DATE to TIMESTAMP.Practical uses include recording birthdays, hire dates, invoice due dates, or any fact that does not need a time component. Because DATE values are smaller than TIMESTAMPs, they save storage and avoid unwanted time zone shifts during serialization or display.Caveats:- Adding or subtracting INTERVAL values must honor calendar rules (leap years, month lengths).- String literals must be in the configured date style or use the ANSI form above to avoid localization problems.- DATE is a reserved keyword in most dialects; aliasing a table or column as DATE without quoting will error.

SQL DATE Syntax

-- DATE literal
DATE 'YYYY-MM-DD'

-- Column definition
column_name DATE

SQL DATE Parameters

Example Queries Using SQL DATE

-- Create a table with a DATE column
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  full_name TEXT,
  hire_date DATE
);

-- Insert using a DATE literal
INSERT INTO employees (full_name, hire_date)
VALUES ('Ada Lovelace', DATE '2023-09-01');

-- Select employees hired this year
SELECT *
FROM employees
WHERE hire_date >= DATE '2023-01-01';

-- Calculate days employed
SELECT id,
       full_name,
       CURRENT_DATE - hire_date AS days_employed
FROM employees;

Expected Output Using SQL DATE

The table is created, a row is inserted with 2023-09-01 stored in the hire_date column, and the SELECT statements return only those rows where hire_date meets the filter or show the computed number of days between CURRENT_DATE and hire_date

Use Cases with SQL DATE

Store birthdays, hire dates, contract start and end dates
Filter records by specific calendar days (e.g., orders placed on 2024-01-15)
Perform age calculations or tenure analytics
Partition tables by day for performance and data retention
Provide a compact, timezone-safe field in APIs and exports

Common Mistakes with SQL DATE

Confusing DATE with DATETIME/TIMESTAMP and expecting time parts to persist
Forgetting that casting DATE to TIMESTAMP sets time to 00:00:00
Using locale-specific string formats (e.g., '05/10/2024') that fail under a different server setting
Naming a column DATE without quoting, causing a syntax error in some databases
Comparing DATE to TIMESTAMP without explicit casting, leading to implicit truncation or mismatched results

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is SQL DATE used for?

DATE is used for values that need only a calendar day, such as birthdays, due dates, or historical events, without time or timezone complications.

What is the valid range of a DATE?

The ANSI standard does not fix a range, but common implementations span 0001-01-01 to 9999-12-31 (MySQL) or wider (PostgreSQL 4713 BC to 5874897 AD).

How do I cast a TIMESTAMP to DATE?

Use CAST(ts AS DATE) or database-specific shorthand (e.g., ts::date in PostgreSQL). The time portion is truncated.

Why does my DATE become midnight when converted to TIMESTAMP?

When a DATE is cast to TIMESTAMP, databases supply a default time of 00:00:00 because DATE contains no time information.