In SQL, there are different data types to help us work with dates and times.
-- create a table with different date and time fields CREATE TABLE Users ( id INT, full_name VARCHAR(50), date_of_birth DATE, last_login DATETIME, registered_at TIMESTAMP ); -- insert values into the Users table. INSERT INTO Users VALUES (1, 'Harry Potter', '1999-04-14', '2022-04-22 10:34:53.44', '2020-03-15 07:31:42.23');
registered_at columns in the
Users table are specified to have the
TIMESTAMP data types respectively.
Date and Time Data Types in SQL
Working with date and time can be tricky because the date formats may vary for different reasons. For example, the United States follows the date format of
mm-dd-yyyy whereas the United Kingdom follows the date format of
Moreover, different database systems use different data types to store date and time. Here's a quick overview of how date and time are stored by different database systems.
There are too many date functions available in each database. However, in this tutorial, we will follow along with commonly used date functions in Microsoft SQL Server.
Query Records Using Dates
We can also run queries to retrieve records filtering by dates. For example,
-- get the records of those teams -- who registered on October 11, 2020 SELECT * FROM Teams WHERE registered = "2020-10-11";
Here, the SQL command returns teams that are registered at
2020-10-11 i.e. October 11, 2020.
Let's take a look at another example,
-- get the records of those teams -- who registered after October 12, 2020 SELECT * FROM Teams WHERE registered > "2022-10-12";
Here, the SQL command selects teams that are registered after the date
Commonly Used Date Functions
This function is used to get the current date and time. For example,
Here, the function returns the current date and time.
This function is used to get the current timestamp in the system. For example,
Here, the function returns the current timestamp in the system.
DATEDIFF(date_part, start_date, end_date)
This function is used to determine the difference between two dates. For example,
SELECT DATEDIFF(month, '2020-12-31 23:59:59', '2022-01-01 00:00:00'); -- output: 13
Here, the function returns the difference between the two dates in months.
The output 13 indicates that there's a difference of 13 months between
2020-12-31 23:59:59 and
You can similarly find the difference in terms of years, days, hours, etc.
DATEADD(date_part, number, date)
This function is used to add a number to a given date part. For example,
SELECT DATEADD(month, 1, '2022-08-31'); -- output: 2022-09-30 00:00:00
Here, the function adds 1 to the month part of the date
DATEADD()functions are not supported by our online editor as it is based on SQLite.
- Although we've only discussed a few functions used in SQL Server, there are several other functions used in other databases. Please refer to each database's documentation for reference.