SQL Date and Time

In SQL, there are different data types to help us work with dates and times.

Example

-- 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');

Here, the date_of_birth, last_login, and registered_at columns in the Users table are specified to have the DATE, DATETIME and 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 dd-mm-yyyy.

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.

Example Format SQL Server Oracle MySQL PostGreSQL
2022-04-22 10:34:23 YYYY-MM-DD hh:mm:ss DATETIME TIMESTAMP
2022-04-22 YYYY-MM-DD DATE DATE DATE
10:34:23 hh:mm:ss.nn TIME TIME TIME TIME
2022-04-22 10:34:23.55 YYYY-MM-DD hh:mm:ss.nn DATETIME TIMESTAMP
2022 YYYY YEAR
12-Jan-22 DD-MON-YY TIMESTAMP

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 2022-10-12 only.


Commonly Used Date Functions

GETDATE()

This function is used to get the current date and time. For example,

SELECT GETDATE();

Here, the function returns the current date and time.


CURRENT_TIMESTAMP

This function is used to get the current timestamp in the system. For example,

SELECT CURRENT_TIMESTAMP;

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 2022-01-01 00:00:00.

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 2022-08-31.

Notes:

  • The GETDATE(), DATEDIFF(), and 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.

Also Read

Did you find this article helpful?