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