MySQL - DATE() Function

MySQL DATE() Function

The MySQL DATE() function is used to retrieve and return the date part of the given datetime expression.

The datetime expression in MySQL is represented in the form of 'YYYY-MM-DD hh:mm:ss'. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Using this function, the date part in this expression, i.e. 'YYYY-MM-DD' will be retrieved and displayed as a result-set.

This function does not accept any arguments, as it is only extracting the current date local to a system.

Syntax

Following is the syntax of MySQL DATE() function −

DATE(expr);

Parameters

This method accepts the date and time expression from which we need to extract the date as a parameter.

Return value

This function extracts the date part from a given date or datetime expression. The return value is a date without the time portion.

Example

Following example demonstrates the usage of the DATE() function −

SELECT DATE('2015-09-05 09:40:45.2300');

Output

Following output is obtained −

DATE('2015-09-05 09:40:45.2300')
2015-09-05

Example

Following is another example of this function −

SELECT DATE('2008-01-02 12:38:48');

Output

Following output is obtained −

DATE('2008-01-02 12:38:48')
2008-01-02

Example

In the following example we are retrieving the date value from the current timestamp −

SELECT DATE(CURRENT_TIMESTAMP);

Output

Following output is obtained −

DATE(CURRENT_TIMESTAMP)
2021-07-12

Example

We can pass the result of the NOW() function as an argument to this function −

SELECT DATE(NOW());

Output

Following output is obtained −

DATE(NOW())
2021-07-12

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Following query retrieves the date values from the DATE column of ORDERS table −

SELECT OID, DATE, DATE(DATE), AMOUNT FROM ORDERS;

Output

The output is displayed as follows −

OID DATE DATE(DATE) AMOUNT
102 2009-10-08 00:00:00 2009-10-08 3000.00
100 2009-10-08 00:00:00 2009-10-08 1500.00
101 2009-11-20 00:00:00 2009-11-20 1560.00
103 2008-05-20 00:00:00 2008-05-20 2060.00
Advertisements