{"id":11276,"date":"2023-10-16T18:28:53","date_gmt":"2023-10-17T01:28:53","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=11276"},"modified":"2023-10-16T18:31:27","modified_gmt":"2023-10-17T01:31:27","slug":"mysql-date-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-date-function\/","title":{"rendered":"MySQL DATE() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL DATE()<\/code> function to extract the date part of a datetime or timestamp value.<\/p>\n\n\n\n

Introduction to MySQL DATE() function<\/h2>\n\n\n\n

The DATE()<\/code> function allows you to extract the date component from a datetime<\/a> or timestamp<\/a> expression.<\/p>\n\n\n\n

Here’s the syntax of the DATE()<\/code> function:<\/p>\n\n\n

DATE(expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
    \n
  • expression<\/code>: This is an expression that evaluates to a DATE<\/code>, a DATETIME<\/code>, or a TIMESTAMP<\/code> value from which you want to extract the value.<\/li>\n<\/ul>\n\n\n\n
    The DATE<\/code>() function returns the DATE<\/code> value. It returns NULL<\/code> if the expression is NULL<\/code>.<\/p>\n\n\n\n
    The DATE()<\/code> function is a convenient tool for working with DATETIME<\/code> and TIMESTAMP<\/code> values in the database. It allows you to extract, filter, group, and perform date arithmetic on date expressions. By using the DATE()<\/code> function in your queries, you can manage and analyze date-related data more effectively.<\/p>\n\n\n\n
    MySQL DATE() function examples<\/h2>\n\n\n\n
    Let’s take some examples of using the MySQL DATE()<\/code> function.<\/p>\n\n\n\n
    1) Extracting a date from a DATETIME value<\/h3>\n\n\n\n
    The following example uses the DATE()<\/code> function to extract the date from a DATETIME<\/code> literal value:<\/p>\n\n\n
    SELECT<\/span> DATE<\/span>('2023-10-17 14:30:45'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------------------------+<\/span>\n| DATE('2023-10-17 14:30:45') |\n+-----------------------------+<\/span>\n| 2023-10-17                  |\n+-----------------------------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, the DATE<\/code>() function returns a DATE<\/code> value ('2023-10-17'<\/code>) from the DATETIME<\/code> value '2023-10-17 14:30:45'<\/code>.<\/p>\n\n\n\n
    2) Using the DATE() function with table data<\/h3>\n\n\n\n
    First, create a new table called events<\/code> with the following structure:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> events<\/span> (\n    id<\/span> INT<\/span> AUTO_INCREMENT PRIMARY KEY<\/span>,\n    time<\/span> DATETIME,\n    name<\/span> VARCHAR<\/span>(255<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The events<\/code> table has three columns id<\/code>, time<\/code>, and name<\/code>. The time column has the DATETIME<\/code> type.<\/p>\n\n\n\n
    Second, insert four rows into the events<\/code> table:<\/p>\n\n\n
    INSERT<\/span> INTO<\/span> events<\/span> (time<\/span>, name<\/span>)\nVALUES<\/span>\n    ('2023-10-15 09:00:00'<\/span>, 'Conference A'<\/span>),\n    ('2023-10-15 18:30:00'<\/span>, 'Conference B'<\/span>),\n    ('2023-10-16 14:00:00'<\/span>, 'Workshop X'<\/span>),\n    ('2023-10-16 19:00:00'<\/span>, 'Workshop Y'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Third, query data from the events<\/code> table:<\/p>\n\n\n
    SELECT<\/span> *\nFROM<\/span> events<\/span>\nWHERE<\/span> DATE<\/span>(time<\/span>) = '2023-10-15'<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +----+---------------------+--------------+<\/span>\n| id | time                | name         |\n+----+---------------------+--------------+<\/span>\n|  1 | 2023-10-15 09:00:00 | Conference A |\n|  2 | 2023-10-15 18:30:00 | Conference B |\n+----+---------------------+--------------+<\/span>\n2 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this example, we use the DATE()<\/code> function to extract the date part of the time column and use its result to filter the events based on a specific date.<\/p>\n\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the DATE()<\/code> function to extract the date part of a datetime or timestamp value.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t