Mastering Dates in SQL: Queries, Formats, and Functions Explained
Write Your First SQL Query in 10 Seconds—Free
If you want to select dates between two specific dates in SQL, you can use the “BETWEEN” operator. Let’s say you have a table named Person with columns for birth dates and death dates. If you want to select all the people who were born between 1990 and 2000 (beginning and end dates included), you can utilize this query:
SELECT * FROM Person WHERE Birthdate BETWEEN '1990-01-01' AND '2000-01-01';
This query will present all the records where the Birthdate is within the specified date range. Besides, if you want to filter the results by two different date columns, you can use the < and > operators. For example, to select all the rows where the Birthdate is after 1990, and the Deathdate is before 2010, you can use this query:
SELECT * FROM Person WHERE Birthdate > '1990-01-01' AND Deathdate < '2010-01-01';
The aforementioned SQL queries are effective to select a date range using the BETWEEN operator and comparison operators.
SQL Date Formats
In SQL, there are various date formats available for use:
-
DATE: The format is YYYY-MM-DD
-
DATETIME: The format is YYYY-MM-DD HH:MI:SS
-
TIMESTAMP: The format is YYYY-MM-DD HH:MI:SS
-
YEAR: The format is YYYY or YY
These formats can help manage date and time values in SQL databases, depending on your needs.
How to Format Dates in SQL Queries
You can format dates in SQL queries using the FORMAT function in SQL Server. This function helps to change the date and time data types into various formats. Here is how to format dates in SQL queries using the FORMAT function:
- To display the date as dd/MM/yyyy, such as 30/06/2022 for June 4, 2022:
SELECT CurrencyCode, Name, FORMAT(ModifiedDate, 'dd/MM/yyyy') AS FormattedDate FROM Sales.Currency;
- To change to a MM/dd/yyyy date format, such as 06/30/2022 for June 4, 2022:
SELECT CurrencyCode, Name, FORMAT(ModifiedDate, 'MM/dd/yyyy') AS FormattedDate FROM Sales.Currency;
- To switch to the yyyy MM dd format:
SELECT CurrencyCode, Name, FORMAT(ModifiedDate, 'yyyy MM dd') AS FormattedDate FROM Sales.Currency;
- To use the yyyyMMdd format:
SELECT CurrencyCode, Name, FORMAT(ModifiedDate, 'yyyyMMdd') AS FormattedDate FROM Sales.Currency;
The above examples showcase how to alter the date formats in SQL queries with the help of the FORMAT function.
SQL provides us with various operators and functions to handle dates and their range effectively. By utilizing the BETWEEN operator, filtering results with < and > operators, and employing various date formats, we can harness the full power of SQL to manage our databases. Additionally, the ‘FORMAT’ function gives us the flexibility to change the date and time data types into various appealing formats as per our requirements. Understanding these functionalities can make the difference between an efficient and struggling SQL user. It’s only a matter of learning them, and the rest is a sweep! Explore how AI2SQL can simplify your SQL query process by effortlessly converting text to SQL queries.
Start your free trial
Share this
More Articles
More Articles
More Articles
TOOLS
Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide
Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide
Feb 5, 2026
TOOLS
OpenClaw AI Assistant: Local 24/7 Automation Guide 2026
OpenClaw AI Assistant: Local 24/7 Automation Guide 2026
Feb 4, 2026
TOOLS
SQL WITH Clause (CTE): Complete Guide with Examples
SQL WITH Clause (CTE): Complete Guide with Examples
Jan 14, 2026
TOOLS
MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion
MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion
Jan 14, 2026
TOOLS
SQL vs Excel: When Should You Make the Switch? [2026]
SQL vs Excel: When Should You Make the Switch? [2026]
Jan 14, 2026
Copyright © AI2sql 2026
Cross Regions Technology
13553 Atlantic Blvd, Suite 201
FL 32225
Company