{"id":25294,"date":"2018-08-18T07:00:22","date_gmt":"2018-08-18T07:00:22","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=25294"},"modified":"2021-03-11T16:56:37","modified_gmt":"2021-03-11T11:26:37","slug":"sql-date-functions","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/","title":{"rendered":"SQL Date Functions | SQL Time Functions"},"content":{"rendered":"

In this tutorial, we will cover in detail about the date and time function in SQL. We have various in-built functions in SQL, which help us filter the useful data from a large set of data. One such part is the date and time functions of SQL.<\/p>\n

Using the date and time functions we can find the date today, we can check the date after some given time and so on.<\/p>\n

We also have the functionality to dissect the given date into various components like the month, the year, or just the day or a combination of all.<\/p>\n

With this added functionality, we are able to utilize SQL for large-scale database designing and maintaining.<\/p>\n

The date and time functions are fascinating and let us understand them more in our tutorial.<\/p>\n

What is Date and Time Function in SQL?<\/h3>\n

In SQL, we have many data types available, which we can use as the date in our table. Some of them popularly being- \u2018YYYY-MM-DD\u2019 and \u2018DD-MM-YYYY\u2019.<\/p>\n

In some scenarios, we also have time stored in our database with the date, in such cases, we need tools to separately access the time and the date. This is where the SQL time and functions come in handy.<\/p>\n

Also, as a beginner, one should be very careful when using date or DateTime in the database as these are very likely to give exceptions if not dealt with properly.<\/p>\n

Formats of Date Time in SQL:<\/strong><\/p>\n

DATE –<\/strong> YYYY-MM-DD
\nDATETIME –<\/strong> YYYY-MM-DD HH:MI:SS
\nTIMESTAMP –<\/strong> YYYY-MM-DD HH:MI:SS
\nYEAR –<\/strong> YYYY or YY<\/p>\n

Why do we need Date and Time Functions?<\/h3>\n

We have plenty of functions available for date and time in SQL. These are provided to make sure smooth access of the date and time module while making and accessing a SQL database.<\/p>\n

Some of the most popular date and time functions are as follows:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Sr.No<\/b><\/td>\nFunction<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
1<\/td>\nNOW( )<\/td>\nDisplays the current date and time.<\/span><\/td>\n<\/tr>\n
2<\/td>\nCURDATE( )<\/td>\nDisplays the current date.<\/span><\/td>\n<\/tr>\n
3<\/td>\nCURTIME( )<\/td>\nDisplays the current time.<\/span><\/td>\n<\/tr>\n
4<\/td>\nDATE( )<\/td>\nDisplays the date from the Date\/DateTime expression.<\/span><\/td>\n<\/tr>\n
5<\/td>\nEXTRACT( )<\/td>\nDisplays selected part i.e. date\/time.<\/span><\/td>\n<\/tr>\n
6<\/td>\nDAY( )<\/td>\nDisplays the day from the given date.<\/span><\/td>\n<\/tr>\n
7<\/td>\nMONTH( )<\/td>\nDisplays the month from the given date.<\/span><\/td>\n<\/tr>\n
8<\/td>\nYEAR( )<\/td>\nDisplays the year from the given date.<\/span><\/td>\n<\/tr>\n
9<\/td>\nDATE_ADD( )<\/td>\nDisplays date after adding the given interval.<\/span><\/td>\n<\/tr>\n
10<\/td>\nDATE_SUB( )<\/td>\nDisplays date after subtracting the given interval.<\/span><\/td>\n<\/tr>\n
11<\/td>\nDATEDIFF( )<\/td>\nDisplays the interval between two dates.<\/span><\/td>\n<\/tr>\n
12<\/td>\nDATE_FORMAT( )<\/td>\nDisplays the date\/time data in various formats available.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Examples of Date Time Functions in SQL<\/h3>\n

Demo Database<\/h4>\n

In this tutorial, we will be using the DataFlair table to understand the concepts. Let us view our demo database:
\nQuery:\u00a0<\/strong><\/p>\n

SELECT * FROM DataFlair;\r\n\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Here we can see the data stored in the DataFlair database.<\/p>\n
Now let us dive into the working of various functions:<\/p>\n
1. NOW( ) in SQL<\/h4>\n
Syntax:<\/strong><\/p>\n
SELECT NOW();\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT NOW() AS dateToday\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Here we can see the result i.e. the date today. You can go ahead and check on your system as well.<\/p>\n
2. CURDATE( ) in SQL<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
SELECT CURDATE();\r\n<\/pre>\n
Example: <\/strong>
\nQuery:<\/strong><\/p>\n
SELECT CURDATE() AS DateToday;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
3. CURTIME( ) in SQL<\/h4>\n
Syntax:<\/strong><\/p>\n
SELECT CURTIME();\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT CURTIME() AS timeNow;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
4. DATE( ) in SQL<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
SELECT DATE(columnName),columnName2 FROM tableName;\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT name , DATE(dob) FROM DataFlair;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
5. EXTRACT( ) in SQL<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
EXTRACT(unit FORM date);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT name, EXTRACT(Day from dob) FROM DataFlair;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
6. DATE_ADD( )<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
DATE_ADD(date, INTERVAL expr type);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT name , DATE_ADD(dob,Interval 2 YEAR) AS post_2_years FROM DataFlair;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
7. DATE_SUB( )<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
DATE_SUB(date, INTERVAL expr type);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT name , DATE_SUB(dob,Interval 1 YEAR) AS post_2_years FROM DataFlair;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
8. DATEDIFF( )<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
DATEDIFF(date1,date2);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT  datediff('2020-2-25','2020-02-01') AS Age;\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
9. DATE_FORMAT( )<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
SELECT DATE_FORMAT(date);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT DATE_FORMAT(NOW(),'%d') as daytoday\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
10. DAY()<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
SELECT DAY(date);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:\u00a0<\/strong><\/p>\n
SELECT DAY(NOW());\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
11. MONTH()<\/h4>\n
Syntax:\u00a0<\/strong><\/p>\n
SELECT MONTH(date);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT MONTH(NOW());\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
12. YEAR( )<\/h4>\n
Syntax:<\/strong><\/p>\n
SELECT YEAR(date);\r\n<\/pre>\n
Example:<\/strong>
\nQuery:<\/strong><\/p>\n
SELECT YEAR(NOW());\r\n<\/pre>\n
\"SQL<\/a><\/p>\n
Summary<\/h3>\n
In this tutorial, we have understood date and time functions in-depth, with the use and syntax of each available to us. With the examples, we were able to understand how powerful SQL date and time functions are.<\/p>\n
Using the date and time functions, we can practically extract any detail we require in a query. With the use of Now() we are able to get the current date and further, we can format the date using the various formatting options available.<\/p>\n","protected":false},"excerpt":{"rendered":"
In this tutorial, we will cover in detail about the date and time function in SQL. We have various in-built functions in SQL, which help us filter the useful data from a large set...<\/p>\n","protected":false},"author":6,"featured_media":86916,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[3554,8986,8989,13442,13443,13444,13545,13546,13572],"class_list":["post-25294","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-date-and-time-functions-in-sql","tag-mysql-date","tag-mysql-timestamp-to-date","tag-sql-date","tag-sql-date-functions","tag-sql-datetime","tag-sql-server-date","tag-sql-server-date-functions","tag-sql-time-format"],"yoast_head":"\nSQL Date Functions | SQL Time Functions - DataFlair<\/title>\n<meta name=\"description\" content=\"SQL Date Functions,Date and Time Functions in SQL,mysql Date,SQL Server Date functions,SQL Datetime,mysql timestamp to date,sql time format\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-date-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Date Functions | SQL Time Functions - DataFlair\" \/>\n<meta property=\"og:description\" content=\"SQL Date Functions,Date and Time Functions in SQL,mysql Date,SQL Server Date functions,SQL Datetime,mysql timestamp to date,sql time format\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-date-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-18T07:00:22+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T11:26:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Date-Time-Function-in-SQL.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Date Functions | SQL Time Functions - DataFlair","description":"SQL Date Functions,Date and Time Functions in SQL,mysql Date,SQL Server Date functions,SQL Datetime,mysql timestamp to date,sql time format","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:\/\/data-flair.training\/blogs\/sql-date-functions\/","og_locale":"en_US","og_type":"article","og_title":"SQL Date Functions | SQL Time Functions - DataFlair","og_description":"SQL Date Functions,Date and Time Functions in SQL,mysql Date,SQL Server Date functions,SQL Datetime,mysql timestamp to date,sql time format","og_url":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-18T07:00:22+00:00","article_modified_time":"2021-03-11T11:26:37+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Date-Time-Function-in-SQL.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Date Functions | SQL Time Functions","datePublished":"2018-08-18T07:00:22+00:00","dateModified":"2021-03-11T11:26:37+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/"},"wordCount":631,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Date-Time-Function-in-SQL.jpg","keywords":["Date and Time Functions in SQL","mysql Date","mysql timestamp to date","SQL date","SQL Date Functions","SQL Datetime","SQL Server date","SQL Server Date functions","sql time format"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-date-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/","url":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/","name":"SQL Date Functions | SQL Time Functions - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Date-Time-Function-in-SQL.jpg","datePublished":"2018-08-18T07:00:22+00:00","dateModified":"2021-03-11T11:26:37+00:00","description":"SQL Date Functions,Date and Time Functions in SQL,mysql Date,SQL Server Date functions,SQL Datetime,mysql timestamp to date,sql time format","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-date-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Date-Time-Function-in-SQL.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/Date-Time-Function-in-SQL.jpg","width":1200,"height":628,"caption":"Date & Time Function in SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-date-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Date Functions | SQL Time Functions"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/25294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=25294"}],"version-history":[{"count":5,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/25294\/revisions"}],"predecessor-version":[{"id":86932,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/25294\/revisions\/86932"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86916"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=25294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=25294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=25294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}