SQL Date and Time

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.

Creating a Table to Store Date and Time

We have to create a column with date data type when creating a table. For example,

-- creating a table with different date fields
CREATE TABLE Users (
  id INT,
  username VARCHAR(50),
  full_name VARCHAR(50),
  date_of_birth DATE,
  last_login DATETIME,
  registered_at TIMESTAMP
);

-- inserting values into the Users table.
INSERT INTO Users
VALUES
(1, 'harry', 'Harry Potter', '1999-04-14', '2022-04-22 10:34:53.44', '2020-03-15 07:31:42.23');

Query Records Using Dates

We can also run queries to retrieve records filtering by dates. For example,

SELECT *
FROM Teams
WHERE registered = "2020-10-11";

Here, the SQL command returns teams that are registered at 2020-10-11.

Let's take a look at another example,

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 number of days difference between two days. For example,

SELECT DATEDIFF(month, '2020-12-31 23:59:59', '2022-01-01 00:00:00');
-- outputs: 13

Here, the function returns the difference between two dates in months.

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');
-- outputs: 2022-09-30 00:00:00

Here, the function adds 1 to month part of the date 2022-08-31.

Note: Although we've only discussed a few functions used in the SQL server, there are several other functions used in different databases. Please refer to each database's documentation for a reference.