SQL - Date & Time

---

In general, time is represented using three values: hours, minutes, and seconds. We can store time in various formats.

• HH:MM:SS (Hours:Minutes:Second)

• It stores and displays time in the day format (AM/PM), for example, 10:00 AM/10:00 PM.

• It (Time) also stores and displays time in 24-hour format; a 24-hour clock runs from 00:00 (midnight) to 23:59.

Date is also represented using three values: date, month, and year. Dates have many possible variations, all of which depend on several inconsistency factors.

• DD/MM/YYYY, For example - 06/02/2023
• MM/DD/YYYY, For example - 02/06/2023
• DD-MM-YYYY, For example - 02-06-2023

Date and Time in SQL

The date and time functions in SQL are used to effectively handle date and time data. While working with a database, the format of the date and time functions should be matched while inserting data into the table.

There are several different date and time functions available in SQL Server. The purpose of include them is to ensure that the date and time module is usable while creating and using databases.

Date & Time data types

Date and time data types are used in SQL for value that must contain both date and time. Various datatypes of date and time are listed in the table below.

S.No.	Datatype & Description	Storage
1	datetime It stores date and time both from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.	8 bytes
2	datetime2 It stores date and time both from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.	6 - 8 bytes
3	smalldatetime It stores date and time both from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.	4 bytes
4	date It stores date only from January 1, 0001 to December 31 9999	3 bytes
5	time It store time only to an accuracy of 100 nanoseconds.	3 - 5 bytes
6	datetimeoffset It is the same of the datetime2 with the addition of the time zone offset.	8 - 10 bytes
7	timestamp It stores the unique number that gets updated every time a row gets created or modified. It does not correspond to real time and is based on internal time. Each table may have only one timestamp variable.

Formats of date and time in SQL

SQL server by default stores date, smalldatetime, timestamp and year values in a certain format. Following are the formats of date and time used in the SQL server −

• DATE - YYYY-MM-DD
• DATETIME - YYYY-MM-DD HH:MI:SS
• SMALLDATETIME - YYYY-MM-DD HH:MI:SS
• TIMESTAMP - a unique number

Inserting date and time values in a table

To insert date and time values in a table, follow the steps below −

• First, you must create a table that accepts date and time values.
• Second, you must insert the data into the newly created table, which accepts date and time data types.

Example

In the following example, we are trying to create a table that accepts only date and time data types using the following query.

CREATE TABLE customers_details(orderDate DATE, shippingDate DATETIME, deliveredDate TIMESTAMP, time TIME);

Verification

After the table has been created, we could use this query to check its details "EXEC sp_help 'dbo.customers_details'" in the SQL.

+----------------+-----------+----------+--------+------+-------+----------+
| Column_name    |	Type	 | Computed | Length | Prec | Scale | Nullable |
+----------------+-----------+----------+--------+------+-------+----------+
|  orderDate     | date      |  no      | 3      |  10  | 0     | Yes      |
|  shippingDate  | datetime  |  no      | 8      |      |       | Yes      |
|  deliveredDate | timestamp |  no      | 8      |      |       | No       |
|  time          | time      |  no      | 5      |  16  | 7     | Yes      |  
+----------------+-----------+----------+--------+------+-------+----------+

To pass the values of all the date and time-related columns in the table, we can use the following query −

INSERT INTO customers_details VALUES('2023-02-01', '2023-02-01 :10:00','2023-02-03 :18:00', '18:00');

Operation on date & time

In this event, we're performing the function and displaying its result, which contains the date and time.

GETDATE() function

The GETDATE() function returns datetime datatypes and is typically used to obtain the current date, as we can see in the following SQL query. GETDATE() it returns the current date with current time.

SELECT GETDATE() AS 'current datetime';

Verification

When we execute the above SQL query, we get the current date with time as follow −

+-------------------------+
| current datetime        |
+-------------------------+
| 2023-02-06 14:10:49.860 |
+-------------------------+

CURRENT_TIMESTAMP

The current_timestamp is used to obtain the current timestamp and it returns the same date and time as the GETDATE() function. Its datatypes are also date and time. As shown in the following SQL query, the CURRENT_TIMESTAMP typically returns the current time along with the current date −

SELECT CURRENT_TIMESTAMP AS 'CURRENTTIMESTAMP';

Output

When we execute the above SQL query, we get the current date with time as follow −

+-------------------------+
| CURRENTTIMESTAMP        |
+-------------------------+
| 2023-02-06 14:31:53.520 |
+-------------------------+

SYSDATETIME() Function

The SYSDATETIME() function is also used to obtain the current time of the system on which the SQL server instance is running. It has larger fractional-second precision compared to the GETDATE() function. Following is the SQL query for the SYSDATETIME() function.

SELECT SYSDATETIME() AS 'Current Time and Date';

Output

When we run the above SQL query, we get the following output −

+-----------------------------+
| Current Time and Date       |
+-----------------------------+
| 2023-02-06 14:57:28.5419381 |
+-----------------------------+

CONVERT() Function

We are extracting the time using the GETDATE() OR CURRENT_TIMESTAMP inside convert function to separate the time component from the current date (from the SYSDATE), as we can see in the SQL query that follow −

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) AS 'HH:MM:SS';

Verification

When we run the above SQL query, we only get the time in "HH:MM:SS" because we are using 108. 108 is a time-only format that displays the time in the "HH:MM:SS" format.

+----------+
| HH:MM:SS |
+----------+
| 15:23:19 |
+----------+