{"id":7032,"date":"2018-08-26T08:23:34","date_gmt":"2018-08-26T15:23:34","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7032"},"modified":"2023-10-22T21:18:01","modified_gmt":"2023-10-23T04:18:01","slug":"mysql-date-functions","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/","title":{"rendered":"MySQL Date Functions"},"content":{"rendered":"\n<p>This page shows you the most commonly used MySQL Date functions that allow you to manipulate date and time data effectively.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 1. Getting the current Date &amp; Time<\/h2>\n\n\n\n<p>This section explains the functions that allow you to retrieve the current date, time, or both.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-curdate\/\">CURDATE()<\/a> &#8211; Return the current date. ( synonyms: CURRENT_DATE() &amp; CURRENT_DATE).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-current_time\/\">CURRENT_TIME<\/a> &#8211; Return the current time ( synonyms: CURRENT_TIME() &amp; CURTIME() ).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-now-function\/\">NOW()<\/a> &#8211; Return the current date and time ( synonyms: CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, LOCALTIME(), LOCALTIMESTAMP()).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-sysdate\/\">SYSDATE()<\/a> &#8211; Return the time at which it executes.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-utc_timestamp\/\">UTC_TIMESTAMP()<\/a> &#8211; Return the current UTC date and time.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-utc_date\/\">UTC_DATE()<\/a> &#8211; Return the current UTC date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-utc_time\/\">UTC_TIME()<\/a> &#8211; Return the current UTC time.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 2. Calculating Date and Time<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-addtime\/\">ADDTIME()<\/a> &#8211;  Add a time interval to a time value or datetime value.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-date_add\/\">DATE_ADD()<\/a> &#8211; Add a time value to a date (synonyms: ADDDATE()).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-date_sub\/\">DATE_SUB()<\/a> &#8211; Subtract a time value (interval) from a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-datediff-function\/\">DATEDIFF()<\/a> &#8211; Return the difference in days of two date values.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-timediff\/\">TIMEDIFF()<\/a> &#8211; Return the difference of two time values.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-timestampadd\/\">TIMESTAMPADD()<\/a> &#8211; Add or subtract an interval from a timestamp or date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-timestampdiff\/\">TIMESTAMPDIFF()<\/a> &#8211; Return the difference between two timestamp values.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-time_to_sec\/\">TIME_TO_SEC()<\/a> &#8211; Return the number of seconds from a time argument.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-to_days\/\">TO_DAYS()<\/a> &#8211; Return a day number (the number of days since year 0) from a given date.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 3. Converting Functions<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-convert_tz\/\">CONVERT_TZ()<\/a> &#8211; Convert a datetime value from one time zone to another.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-from_days\/\">FROM_DAYS()<\/a>  &#8211; Convert a numeric day count into a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-str_to_date\/\">STR_TO_DATE()<\/a> &#8211; Convert a string to date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-from_unixtime\/\">FROM_UNIXTIME()<\/a> &#8211; Convert UNIX timestamps into a readable date and time format.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-unix_timestamp\/\">UNIX_TIMESTAMP()<\/a> &#8211; Convert a datetime to a UNIX timestamp.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 4. Formatting Date &amp; Time functions<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-date_format\/\">DATE_FORMAT()<\/a> &#8211; Return a string representation of a date based on a format.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-time_format-function\/\">TIME_FORMAT()<\/a> &#8211; Return a string representation of a time based on a format.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-get_format\/\">GET_FORMAT()<\/a> &#8211; Return a format string for a date, time, datetime, or timestamp.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 5. Extracting Date &amp; Time Functions<\/h2>\n\n\n\n<p>The extraction functions allow you to extract date and time components from a date and time.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-date-function\/\">DATE()<\/a> &#8211; Extract the date component from a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-extract\/\">EXTRACT()<\/a> &#8211; Extract a component of a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-year\/\">YEAR()<\/a> &#8211; Return the year component of a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-yearweek\/\">YEARWEEK()<\/a> &#8211; Return the year and week for a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-quarter\/\">QUARTER()<\/a> &#8211; Return the quarter of the year for a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-month\/\">MONTH()<\/a> &#8211; Return the month component of a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-week\/\">WEEK()<\/a> &#8211; Return the week component of a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-weekday\/\">WEEKDAY()<\/a> &#8211; Return the weekday index of a date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-week\/\">WEEKOFYEAR()<\/a> &#8211; Return the calendar week of the date (1-53) &#8211; equivalent to  WEEK(date, 3).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-day\/\">DAY()<\/a> &#8211; Return the day of the month for a specific date (1-31). DAYOFMONTH is the synonym for DAY.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-dayofyear\/\">DAYOFYEAR()<\/a> &#8211; Return the day of the year (1-366).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-dayofweek\/\">DAYOFWEEK()<\/a> &#8211; Return the day of the week (1-7).<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-hour\/\">HOUR()<\/a> &#8211; Return the hour for a time.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-minute-function\/\">MINUTE()<\/a> &#8211; Return the minute for a time.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-second\/\">SECOND()<\/a> &#8211; Return the second for a time.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-last_day\/\">LAST_DAY()<\/a> &#8211; Return an integer that represents the last day of the month for a specific date.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 6. Getting Month &amp; Day Names<\/h2>\n\n\n\n<p>This section shows you how to use functions to get the month and day names.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-dayname\/\">DAYNAME()<\/a> &#8211; Return the name of the day for a specific date.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-monthname\/\">MONTHNAME()<\/a> &#8211; Return the name of the month for a specific date.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 7. Creating Date &amp; Time Functions<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-makedate\/\">MAKEDATE()<\/a> &#8211; create a date based on a given year and the number of days.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-maketime\/\">MAKETIME()<\/a> &#8211; create a time based on hour, minute, and second.<\/li>\n<\/ul>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<h2 class=\"wp-block-heading\">Section 8. Handling Period Functions<\/h2>\n\n\n\n<p>This section covers the function that manipulates the periods in the format YYMM or YYMMMM.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-period_add\/\">PERIOD_ADD()<\/a> &#8211; add a number of months to a period in the format YYMM or YYMMMM.<\/li>\n\n\n\n<li><a href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/mysql-period_diff\/\">PERIOD_DIFF()<\/a> &#8211; calculate the difference in months of two periods represented in the format YYMM or YYYYMM.<\/li>\n<\/ul>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.<\/p>\n","protected":false},"author":2,"featured_media":10061,"parent":0,"menu_order":17,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-7032","page","type-page","status-publish","has-post-thumbnail","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL Date Functions<\/title>\n<meta name=\"description\" content=\"This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Date Functions\" \/>\n<meta property=\"og:description\" content=\"This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-23T04:18:01+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/\",\"name\":\"MySQL Date Functions\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/mysql-date-functions.svg\",\"datePublished\":\"2018-08-26T15:23:34+00:00\",\"dateModified\":\"2023-10-23T04:18:01+00:00\",\"description\":\"This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/mysql-date-functions.svg\",\"contentUrl\":\"https:\\\/\\\/www.mysqltutorial.org\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/mysql-date-functions.svg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-date-functions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Date Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL Date Functions","description":"This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Date Functions","og_description":"This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.","og_url":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-10-23T04:18:01+00:00","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/","url":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/","name":"MySQL Date Functions","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/#primaryimage"},"image":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2021\/04\/mysql-date-functions.svg","datePublished":"2018-08-26T15:23:34+00:00","dateModified":"2023-10-23T04:18:01+00:00","description":"This page shows you the most commonly used MySQL Date Functions that allow you to manipulate date and time data effectively.","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-date-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/#primaryimage","url":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2021\/04\/mysql-date-functions.svg","contentUrl":"https:\/\/www.mysqltutorial.org\/wp-content\/uploads\/2021\/04\/mysql-date-functions.svg"},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-date-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Date Functions"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/7032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=7032"}],"version-history":[{"count":5,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/7032\/revisions"}],"predecessor-version":[{"id":11502,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/7032\/revisions\/11502"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media\/10061"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=7032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}