sql

Khóa học miễn phí SQL – Date & Time nhận dự án làm có lương

Posted on by user

SQL – Date & Time

Table of content

SQL provides multiple datatypes and functions to handle Date and Time values in a database. This is because Date and Time values are represented in various formats. For instance, there are two common ways to represent a date value: DD/MM/YYYY and MM/DD/YYYY. Similarly, there is more than a single way to represent time values.

For a database to recognize such data given in any format, we make use of multiple datatypes and functions.

The only tricky part about storing the Date and Time data in a database is making sure that the values are inserted in the tables with the same format as the datatype.

Different database systems use different datatypes and functions to store and handle the Date and Time data.

Date & Time Datatypes in SQL

Date and time datatypes are used in SQL to store date and time values in various formats. The datatypes available in SQL are listed 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; with the format: YYYY-MM-DD HH:MI:SS.

 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. It is stored in the format of YYYY-MM-DD HH:MI:SS.

 4 bytes
4

date

It stores date only from January 1, 0001 to December 31 9999, in the format: YYYY-MM-DD.

 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.

Example

In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats.

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

To insert values into this table, use the following query −

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

Output

The table will be created as follows −

orderDate shippingDate deliveredDate time
2023-02-01 2023-02-01 :10:00 2023-02-03 :18:00 18:00

Date & Time Functions in SQL

SQL also provides multiple functions to handle date and time values.

For instance, there are different functions to retrieve the current timestamp in different formats. Let us see some of such functions below −

CURDATE() Function

To get the current date, we use the CURDATE() function in MySQL. The format of the resultant date will be ”YYYY-MM-DD” (string) or YYYYMMMDD (numeric).

SELECT CURDATE();

Output

When we execute the above query, we get the current days date −

CURDATE()
2023-08-22

NOW() Function

The MySQL NOW() function will retrieve the current date and time value as a timestamp based on the context and, the value returned will be in either of the two formats: ”YYYY-MM-DD hh:mm:ss” and ”YYYYMMDDhhmmss”.

SELECT NOW();

Output

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

NOW()
2023-08-22 15:30:25

CURRENT_TIMESTAMP() Function

The MySQL CURRENT_TIMESTAMP() function is used to get the current timestamp. The value returned will be in ”YYYY-MM-DD hh:mm:ss” (string) or YYYYMMDDhhmmss (numeric) format. This function is a synonym for NOW().

SELECT CURRENT_TIMESTAMP();

Output

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

CURRENT_TIMESTAMP()
2023-08-22 15:31:32

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user