\n| WEEK<\/td>\n | WK or WW<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n The examples below are sample calls to the function. The inputs puts are valid date part parameters and date time variables. <\/p>\n -- \r\n-- Find the difference in date parts (unit of measure)\r\n--\r\n\r\n-- Years\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = '2014-12-10 12:32:10.12';\r\nSELECT \r\n DATEDIFF(YYYY, @DT1, @DT2) AS DIFF_YR1,\r\n DATEDIFF(YY, @DT1, @DT2) AS DIFF_YR2\r\nGO\r\n\r\n-- Quarters\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = '2014-06-10 12:32:10.12';\r\nSELECT \r\n DATEDIFF(QQ, @DT1, @DT2) AS DIFF_QTR1,\r\n DATEDIFF(Q, @DT1, @DT2) AS DIFF_QTR2\r\nGO\r\n\r\n-- Months\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = '2014-06-10 12:32:10.12';\r\nSELECT \r\n DATEDIFF(MM, @DT1, @DT2) AS DIFF_MM1,\r\n DATEDIFF(M, @DT1, @DT2) AS DIFF_MM2\r\nGO\r\n\r\n-- Day of year (days)\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = '2014-06-10 12:32:10.12';\r\nSELECT \r\n DATEDIFF(DY, @DT1, @DT2) AS DIFF_DOY1,\r\n DATEDIFF(Y, @DT1, @DT2) AS DIFF_DOY2\r\nGO\r\n\r\n-- Days\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = '2014-06-10 12:32:10.12';\r\nSELECT \r\n DATEDIFF(DD, @DT1, @DT2) AS DIFF_DD1,\r\n DATEDIFF(D, @DT1, @DT2) AS DIFF_DD2\r\nGO\r\n\r\n-- Weeks\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = '2014-06-10 12:32:10.12';\r\nSELECT \r\n DATEDIFF(WK, @DT1, @DT2) AS DIFF_WK1,\r\n DATEDIFF(WW, @DT1, @DT2) AS DIFF_WK2\r\nGO\r\n\r\n<\/pre>\n<\/p>\nThe output from the test is listed below. <\/p>\n \r\noutput: \r\n\r\nDIFF_YR1 DIFF_YR2\r\n----------- -----------\r\n1 1\r\n\r\nDIFF_QTR1 DIFF_QTR2\r\n----------- -----------\r\n2 2\r\n\r\nDIFF_MM1 DIFF_MM2\r\n----------- -----------\r\n6 6\r\n\r\nDIFF_DOY1 DIFF_DOY2\r\n----------- -----------\r\n182 182\r\n\r\nDIFF_DD1 DIFF_DD2\r\n----------- -----------\r\n182 182\r\n\r\nDIFF_WK1 DIFF_WK2\r\n----------- -----------\r\n26 26\r\n\r\n<<\/pre>\n<\/p>\nLike any good unit test, one should try both positive and negative test cases. I am not going to explore how an EMPTY string or NULL value effect the results provided by the function. <\/p>\n However, the following rules apply. Any invalid date parameter results in a raised error. Any NULL values passed as parameters result in a NULL result. Any EMPTY strings result in a default date.<\/p>\n To make a long story short, pass one of the six date part parameters with two date\/time variables to calculated the difference. Next time, I will be talking about how to use this function with Time Related Parts.<\/p>\n","protected":false},"excerpt":{"rendered":" I am unfluctuating in my creation of short articles or tidbits on Transaction SQL date\/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Many times you will want to calculate the difference between two given dates using a particular unit of measure (date part) like day, week, month, quarter or year. Today, I will be exploring the DATEDIFF() function. This function has 12 different input parameters, units of measure (date part), that can be passed to change the resulting output. To summarize,…<\/p>\n","protected":false},"author":1,"featured_media":6617,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[991,31,987,940,992,15,29],"class_list":["post-6718","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-compute-date-difference","tag-database-developer","tag-date-related-parts","tag-date-time-functions","tag-datediff","tag-john-f-miner-iii","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6718","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6718"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6718\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6617"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6718"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6718"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6718"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |