SQL Date and Time

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

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.

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.