SQL Dates

SQL Dates is a feature in SQL that allows you to store, filter, and manipulate date and time values in your tables. These date-related fields are useful for tracking when events happen like order dates, birthdates, or appointment times.

In SQL, handling dates correctly is important because query results depend on how the date values are stored (with or without time) and whether the format matches. Many common tasks in SQL involve comparing dates, finding records within a certain range, or calculating differences between dates.

SQL Dates Syntax

SQL provides several date and time data types to store information related to dates and timestamps. The most commonly used ones include:

Common SQL Date Data Types:

Important: Date/time handling and functions may vary across databases. Our playground supports basic DATE and DATETIME types, but functions like GETDATE() or CURRENT_TIMESTAMP may not work, depending on the SQL engine behind our playground. Always test and adjust accordingly.

SQL Dates Example

Let’s create a simple Orders table with a DATE column and insert some sample records:

CREATE TABLE Orders (
  order_id INTEGER PRIMARY KEY,
  product_name VARCHAR(100),
  order_date DATE
);

CREATE TABLE Orders (

  order_id INTEGER PRIMARY KEY,

  product_name VARCHAR(100),

  order_date DATE

);

Now insert some rows into the table:

INSERT INTO Orders (order_id, product_name, order_date) VALUES
(1, 'Geitost', '2008-11-11'),
(2, 'Camembert Pierrot', '2008-11-09'),
(3, 'Mozzarella di Giovanni', '2008-11-11'),
(4, 'Mascarpone Fabioli', '2008-10-29');

INSERT INTO Orders (order_id, product_name, order_date) VALUES

(1, 'Geitost', '2008-11-11'),

(2, 'Camembert Pierrot', '2008-11-09'),

(3, 'Mozzarella di Giovanni', '2008-11-11'),

(4, 'Mascarpone Fabioli', '2008-10-29');

Query to filter by date:

This query returns all orders made on November 11, 2008:

SELECT * FROM Orders
WHERE order_date = '2008-11-11';

SELECT * FROM Orders

WHERE order_date = '2008-11-11';

SQL Dates With Time Components

If your order_date column includes a time (i.e., is stored as a DATETIME), filtering by just the date still works in our playground:

SELECT * FROM Orders
WHERE order_date = '2008-11-11';

SELECT * FROM Orders

WHERE order_date = '2008-11-11';

This will return records where the date matches 2008-11-11, even if the time is included.

In other SQL environments, if exact matching fails due to time, you can use functions like:

SELECT * FROM Orders
WHERE DATE(order_date) = '2008-11-11';

SELECT * FROM Orders

WHERE DATE(order_date) = '2008-11-11';

Or use a range:

SELECT * FROM Orders
WHERE order_date >= '2008-11-11' AND order_date < '2008-11-12';

SELECT * FROM Orders

WHERE order_date >= '2008-11-11' AND order_date < '2008-11-12';

SQL Dates Visual Diagram

SQL Dates Labs