{"id":9649,"date":"2019-02-20T13:28:31","date_gmt":"2019-02-20T13:28:31","guid":{"rendered":"https:\/\/dbtut.com\/?p=9649"},"modified":"2019-02-20T13:28:31","modified_gmt":"2019-02-20T13:28:31","slug":"datediff-and-datediff_big-functions-in-sql-servertsql","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/","title":{"rendered":"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL)"},"content":{"rendered":"

 <\/p>\n

We can find the difference between the two dates using the following dateparts with the DATEDIFF function. The return data type is int.<\/p>\n

If the int data type is insufficient for the difference between two dates, you should use the DATEDIFF_BIG function. You can use it in the same way as DATEDIFF function. Only return data type is bigint.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
datepart<\/td>\nAbbreviations<\/td>\n<\/tr>\n
year<\/td>\nyy,\u00a0yyyy<\/td>\n<\/tr>\n
quarter<\/td>\nqq,\u00a0q<\/td>\n<\/tr>\n
month<\/td>\nmm,\u00a0m<\/td>\n<\/tr>\n
dayofyear<\/td>\ndy,\u00a0y<\/td>\n<\/tr>\n
day<\/td>\ndd,\u00a0d<\/td>\n<\/tr>\n
week<\/td>\nwk,\u00a0ww<\/td>\n<\/tr>\n
weekday<\/td>\ndw,\u00a0w<\/td>\n<\/tr>\n
hour<\/td>\nhh<\/td>\n<\/tr>\n
minute<\/td>\nmi,\u00a0n<\/td>\n<\/tr>\n
second<\/td>\nss,\u00a0s<\/td>\n<\/tr>\n
millisecond<\/td>\nms<\/td>\n<\/tr>\n
microsecond<\/td>\nmcs<\/td>\n<\/tr>\n
nanosecond<\/td>\nns<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Let’s make examples for a better understanding of DATEDIFF Function.<\/p>\n

Find the month difference between two dates:<\/strong><\/p>\n

SELECT DATEDIFF(month,'2019-01-01','2022-03-01') AS month_DATEPART,\r\n\u00a0\u00a0     DATEDIFF(m,'2019-01-01','2022-03-01') AS m_DATEPART,\r\n\u00a0\u00a0     DATEDIFF(mm,'2019-01-01','2022-03-01') AS mm_DATEPART<\/pre>\n
\"\"<\/p>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  We can find the difference between the two dates using the following dateparts with the DATEDIFF function. The return data type is int. If the int data type is insufficient for the difference between two dates, you should use the DATEDIFF_BIG function. You can use it in the same way as DATEDIFF function. Only …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3,1596],"tags":[2077,2078],"class_list":["post-9649","post","type-post","status-publish","format-standard","","category-mssql","category-tsql","tag-datediff-function","tag-datediff_big-function"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":282,"today_views":0},"yoast_head":"\nDATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL) - Database Tutorials<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL) - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"  We can find the difference between the two dates using the following dateparts with the DATEDIFF function. The return data type is int. If the int data type is insufficient for the difference between two dates, you should use the DATEDIFF_BIG function. You can use it in the same way as DATEDIFF function. Only …\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-20T13:28:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL)\",\"datePublished\":\"2019-02-20T13:28:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\"},\"wordCount\":127,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png\",\"keywords\":[\"DATEDIFF Function\",\"DATEDIFF_BIG Function\"],\"articleSection\":[\"MSSQL\",\"TSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\",\"name\":\"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL) - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png\",\"datePublished\":\"2019-02-20T13:28:31+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png\",\"width\":871,\"height\":263},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL) - Database Tutorials","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:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/","og_locale":"en_US","og_type":"article","og_title":"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL) - Database Tutorials","og_description":"  We can find the difference between the two dates using the following dateparts with the DATEDIFF function. The return data type is int. If the int data type is insufficient for the difference between two dates, you should use the DATEDIFF_BIG function. You can use it in the same way as DATEDIFF function. Only …","og_url":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/","og_site_name":"Database Tutorials","article_published_time":"2019-02-20T13:28:31+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL)","datePublished":"2019-02-20T13:28:31+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/"},"wordCount":127,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png","keywords":["DATEDIFF Function","DATEDIFF_BIG Function"],"articleSection":["MSSQL","TSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/","url":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/","name":"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL) - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png","datePublished":"2019-02-20T13:28:31+00:00","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/02\/img_5c6bb0a9a8967.png","width":871,"height":263},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/02\/20\/datediff-and-datediff_big-functions-in-sql-servertsql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"DATEDIFF and DATEDIFF_BIG Functions in SQL Server(TSQL)"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9649","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=9649"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/9649\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=9649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=9649"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=9649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}