{"id":11430,"date":"2023-10-19T17:28:35","date_gmt":"2023-10-20T00:28:35","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-get_format\/"},"modified":"2023-10-19T18:19:27","modified_gmt":"2023-10-20T01:19:27","slug":"mysql-get_format","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-get_format\/","title":{"rendered":"MySQL GET_FORMAT() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL GET_FORMAT()<\/code> function to return a format string.<\/p>\n\n\n\n

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

The GET_FORMAT()<\/code> function lets you get a format string of a DATE<\/a><\/code>, TIME<\/a><\/code>, DATETIME<\/a><\/code>, or TIMESTAMP<\/a><\/code>.<\/p>\n\n\n\n

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

GET_FORMAT(\n   {DATE|TIME|DATETIME|TIMESTAMP}, \n   {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'}\n)<\/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
  • The first argument specifies the date and time you want to get the format string. It can be DATE<\/code>, TIME<\/code>, DATETIME<\/code>, or TIMESTAMP<\/code>.<\/li>\n\n\n\n
  • The second argument determines the desired format style ‘EUR<\/code>‘, ‘USA<\/code>‘, ‘JIS<\/code>‘, ‘ISO<\/code>‘, and ‘INTERNAL<\/code>‘<\/li>\n<\/ul>\n\n\n\n
    Note that the \u00a0ISO<\/code> format refers to ISO 9075<\/code>, not ISO 8601<\/code>.<\/p>\n\n\n\n
    The GET_FORMAT()<\/code> function returns a format string.<\/p>\n\n\n\n
    In practice, you use the GET_FORMAT()<\/code> function with the DATE_FORMAT()<\/code> and STR_TO_DATE()<\/code> functions to format and parse dates and times in different styles.<\/p>\n\n\n\n
    MySQL GET_FORMAT() function examples<\/h2>\n\n\n\n
    Let’s take some examples of using the DATE_FORMAT()<\/code> function.<\/p>\n\n\n\n
    1) Using GET_FORMAT() function with DATE example<\/h3>\n\n\n\n
    The following example uses the GET_FORMAT()<\/code> function with the DATE_FORMAT()<\/code> function to format a date in European style:<\/p>\n\n\n
    SELECT<\/span> \n  DATE_FORMAT<\/span>(\n    '2023-10-19'<\/span>, \n    GET_FORMAT<\/span>(DATE<\/span>, 'EUR'<\/span>)\n  ) AS<\/span> formatted_date;<\/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| formatted_date |\n+----------------+<\/span>\n| 19.10.2023     |\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, we used the GET_FORMAT()<\/code> function to get the date format in European style and the returned format string to the DATE_FORMAT()<\/code> function to format the date '2023-10-19'<\/code>.<\/p>\n\n\n\n
    2) Using GET_FORMAT() function with TIME example<\/h3>\n\n\n\n
    The following example uses the GET_FORMAT()<\/code> function with TIME_FORMAT()<\/code> function to format a time value:<\/p>\n\n\n
    SELECT<\/span> \n  TIME_FORMAT<\/span>(\n    '14:30:15'<\/span>, \n    GET_FORMAT<\/span>(TIME<\/span>, 'INTERNAL'<\/span>)\n  ) AS<\/span> formatted_time;<\/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| formatted_time |\n+----------------+<\/span>\n| 143015         |\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
    3) Using GET_FORMAT() function with DATETIME example<\/h3>\n\n\n\n
    The following example uses the GET_FORMAT()<\/code> function to format a datetime value in ISO9075<\/code> style:<\/p>\n\n\n
    SELECT<\/span> \n  DATE_FORMAT<\/span>(\n    '2023-10-19 15:30:00'<\/span>, \n    GET_FORMAT<\/span>(DATETIME, 'ISO'<\/span>)\n  ) AS<\/span> formatted_datetime;<\/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| formatted_datetime  |\n+---------------------+<\/span>\n| 2023-10-19 15:30:00 |\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
    4) Using GET_FORMAT() function with TIMESTAMP example<\/h3>\n\n\n\n
    The following example uses the GET_FORMAT()<\/code> function with TIMESTAMP<\/code> value to format timestamps in USA<\/code> style:<\/p>\n\n\n
    SELECT<\/span> \n  DATE_FORMAT<\/span>(\n    '2023-10-19 15:30:00'<\/span>, \n    GET_FORMAT<\/span>(TIMESTAMP<\/span>, 'USA'<\/span>)\n  ) AS<\/span> formatted_timestamp;<\/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| formatted_timestamp |\n+---------------------+<\/span>\n| 2023-10-19 15.30.00 |\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
    Summary<\/h2>\n\n\n\n
      \n
    • Use the GET_FORMAT()<\/code> function to get a format string for a DATE<\/code>, TIME<\/code>, DATETIME<\/code>, and TIMESTAMP<\/code>.<\/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