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.
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
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 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.