{"id":340,"date":"2024-08-26T06:04:59","date_gmt":"2024-08-26T06:04:59","guid":{"rendered":"https:\/\/techkubo.com\/sql\/?p=340"},"modified":"2025-05-20T07:07:21","modified_gmt":"2025-05-20T07:07:21","slug":"sql-dates","status":"publish","type":"post","link":"https:\/\/techkubo.com\/sql\/sql-dates\/","title":{"rendered":"SQL Dates"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL Dates Syntax<\/h2>\n\n\n\n

SQL provides several date and time data types to store information related to dates and timestamps. The most commonly used ones include:<\/p>\n\n\n\n

Common SQL Date Data Types:<\/h3>\n\n\n\n
Data Type<\/th>Format<\/th>Notes<\/th><\/tr><\/thead>
DATE<\/code><\/td>YYYY-MM-DD<\/code><\/td>Only the date<\/td><\/tr>
DATETIME<\/code><\/td>YYYY-MM-DD HH:MI:SS<\/code><\/td>Includes both date and time<\/td><\/tr>
TIMESTAMP<\/code><\/td>YYYY-MM-DD HH:MI:SS<\/code><\/td>Automatically updated in some systems<\/td><\/tr>
YEAR<\/code> (MySQL)<\/td>YYYY<\/code> or YY<\/code><\/td>Only the year (not available in all systems)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n

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

<\/p>\n\n\n\n

SQL Dates Example<\/h2>\n\n\n\n

Let\u2019s create a simple Orders<\/code> table with a DATE<\/code> column and insert some sample records:<\/p>\n\n\n\n

CREATE TABLE Orders (\n  order_id INTEGER PRIMARY KEY,\n  product_name VARCHAR(100),\n  order_date DATE\n);<\/pre><\/div>\n\n\n\n
Now insert some rows into the table:<\/p>\n\n\n\n
INSERT INTO Orders (order_id, product_name, order_date) VALUES\n(1, 'Geitost', '2008-11-11'),\n(2, 'Camembert Pierrot', '2008-11-09'),\n(3, 'Mozzarella di Giovanni', '2008-11-11'),\n(4, 'Mascarpone Fabioli', '2008-10-29');<\/pre><\/div>\n\n\n\n
Query to filter by date:<\/h3>\n\n\n\n
This query returns all orders made on November 11, 2008<\/strong>:<\/p>\n\n\n\n
SELECT * FROM Orders\nWHERE order_date = '2008-11-11';<\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Try It Out!<\/a><\/div>\n<\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Dates With Time Components <\/h2>\n\n\n\n
If your order_date<\/code> column includes a time (i.e., is stored as a DATETIME<\/code>), filtering by just the date still works in our playground:<\/p>\n\n\n\n
SELECT * FROM Orders\nWHERE order_date = '2008-11-11';<\/pre><\/div>\n\n\n\n
This will return records where the date matches 2008-11-11<\/code>, even if the time is included.<\/p>\n\n\n\n
In other SQL environments, if exact matching fails due to time, you can use functions like:<\/p>\n\n\n\n
SELECT * FROM Orders\nWHERE DATE(order_date) = '2008-11-11';<\/pre><\/div>\n\n\n\n
Or use a range:<\/p>\n\n\n\n
SELECT * FROM Orders\nWHERE order_date >= '2008-11-11' AND order_date < '2008-11-12';<\/pre><\/div>\n\n\n\n
<\/p>\n\n\n\n
SQL Dates Visual Diagram<\/h2>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
SQL Dates Labs<\/h2>\n\n\n\n