{"id":105,"date":"2018-06-16T18:00:47","date_gmt":"2018-06-16T18:00:47","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=105"},"modified":"2018-06-17T11:51:03","modified_gmt":"2018-06-17T11:51:03","slug":"dates-in-sql-server-adding-subtracting","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/","title":{"rendered":"Dates In SQL Server: Adding & subtracting"},"content":{"rendered":"

Different DBMS systems have different ways of managing dates. As I’m doing a series of articles on dates, I thought I’d throw some notes down about calculating the difference between some of them.<\/p>\n

Testing dataset<\/h1>\n

Picking up from my article on\u00a0Dates In SQL Server: Create Sample Date Ranges<\/a>\u00a0I’ll use a sample from there to create a date range for testing and demonstration for this article:<\/p>\n

--------------------------------------------------------------------------------\r\n-- Dates table\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE testing_date_list\r\n(\r\n    TheDate DATE NOT NULL PRIMARY KEY\r\n);\r\n\r\n\r\n--------------------------------------------------------------------------------\r\n-- Custom Date Range Using Number sequence\r\n--------------------------------------------------------------------------------\r\n\r\nDECLARE @DateStart DATE = '01-DEC-17';\r\nDECLARE @DateEnd   DATE = '07-FEB-18';\r\n \r\nPRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);\r\nPRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);\r\n\r\nWITH number_tbl AS (\r\nSELECT 0 AS a UNION ALL\r\nSELECT 1 UNION ALL\r\nSELECT 2 UNION ALL\r\nSELECT 3 UNION ALL\r\nSELECT 4 UNION ALL\r\nSELECT 5 UNION ALL\r\nSELECT 6 UNION ALL\r\nSELECT 7 UNION ALL\r\nSELECT 8 UNION ALL\r\nSELECT 9\r\n), number_sequences AS (\r\nSELECT t1.a AS t1a,\r\n       t2.a AS t2a,\r\n       t3.a AS t3a,\r\n       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation\r\n  FROM number_tbl t1\r\n  CROSS JOIN number_tbl t2\r\n  CROSS JOIN number_tbl t3\r\n)\r\nINSERT\r\n  INTO testing_date_list\r\n      (TheDate)\r\nSELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]\r\n  FROM number_sequences\r\n WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd\r\n ORDER BY concatenate_calculation\r\n;<\/pre>\n
Addition, subtraction & rounding<\/h1>\n
Basic addition and subtraction requires the\u00a0DateAdd()<\/a> function, which has the syntax:<\/p>\n
DATEADD (<datepart> , <number> , <date> )<\/pre>\n
While people can use simple arithmetic functions on dates in SQL Server, I’d always recommend against it. Regional settings and other changes can suddenly alter the behaviour of queries.<\/p>\n
Rounding is commonly seen using a combination of DateDiff (more detail below) and DateAdd(). The takeaway is that to round in SQL server (historically) one calculated the difference in days (or whatever we are rounding to) between the current day (or whatever we want to round to), and 0. Then added that difference to 0. This has become the default way people do rounding, an alternative (more legible, but perhaps less performant) is to use DateFromParts()<\/a>:<\/p>\n
DateFromParts(<year>, <month>, <day>)\r\n<\/pre>\n
A full set of examples are:<\/p>\n
--------------------------------------------------------------------------------\r\n-- Addition, Subtraction & Rounding\r\n--------------------------------------------------------------------------------\r\nSELECT [TheDate],\r\n     --Adding & Subtracting\r\n       DateAdd(DAY, 1, [TheDate]) AS TheDate_DateAdd_1_day,\r\n       DateAdd(DAY,-1, [TheDate]) AS TheDate_DateAdd_sub_1_day,\r\n     --Rounding\r\n       DateAdd(MONTH, DateDiff(MONTH, 0, [TheDate]), 0)    AS TheDate_WholeMonth,\r\n       DateAdd(YEAR,  DateDiff(YEAR,  0, [TheDate]), 0)    AS TheDate_WholeYear,\r\n       DateFromParts(YEAR([TheDate]), MONTH([TheDate]), 1) AS TheDate_WholeMonth_DFP,\r\n     --Subtracting and Rounding\r\n       DateAdd(MONTH, -3, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS TheDate_ThreeMonthsBeforeStart,\r\n       DateAdd(MONTH,-13, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS TheDate_PriorYearMonthOnMonth   -- 1 year of months from last month\r\n  FROM testing_date_list\r\n;<\/pre>\n
Finding the\u00a0difference<\/h1>\n
To answer questions like “how many days ago was X” or “How long did it take between @StartPoint and @EndPoint”. We can use DateDiff()<\/a>.<\/p>\n
DATEDIFF (<datepart>, <startdate>, <enddate>)<\/pre>\n
This will give us an INT value of the number of <datepart> between the two dates. If you are expecting a lot back, consider DateDiffBig()<\/a>.<\/p>\n
Here are some examples looking at dates:<\/p>\n
--------------------------------------------------------------------------------\r\n-- Difference\r\n--------------------------------------------------------------------------------\r\nSELECT [TheDate],\r\n     --Difference\r\n       DateDiff(DAY,   GetDate(), [TheDate]) AS DiffFromNow_Days,\r\n       DateDiff(MONTH, GetDate(), [TheDate]) AS DiffFromNow_Months,\r\n       DateDiff(YEAR,  GetDate(), [TheDate]) AS DiffFromNow_Years,\r\n       DateDiff(HOUR,  GetDate(), [TheDate]) AS DiffFromNow_Hours\r\n  FROM testing_date_list\r\n;<\/pre>\n
Calculating duration<\/h2>\n
Another good example is finding out “how long did X take”:<\/p>\n
--------------------------------------------------------------------------------\r\n-- Difference between start and end\r\n-- 750 seconds is 12.5 minits\r\n--------------------------------------------------------------------------------\r\nDECLARE @DateTimeStart DATETIME2(4) = DateAdd(SECOND, -750, GetDate());\r\nDECLARE @DateTimeEnd   DATETIME2(4) = GetDate();\r\n\r\nSELECT @DateTimeStart AS [start],\r\n       @DateTimeEnd   AS [end],\r\n       DateDiff(SECOND, @DateTimeStart, @DateTimeEnd) AS DiffInSeconds,\r\n     --Note: the minutes are rounded up here\r\n       DateDiff(MINUTE, @DateTimeStart, @DateTimeEnd) AS DiffInMinutes,\r\n     --To format the time:\r\n     -- - Calculate the number of seconds\r\n     -- - Multiply that by 1000 so we get milliseconds\r\n     -- - Add to 0\r\n     -- - Only output the time portion\r\n       CONVERT(VARCHAR, DateAdd(MILLISECOND, DateDiff(SECOND, @DateTimeStart, @DateTimeEnd) * 1000, 0), 114) AS FormattedTime\r\n;<\/pre>\n
 <\/p>\n
See it all in action over on\u00a0rextester.com<\/a><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Different DBMS systems have different ways of managing dates. As I’m doing a series of articles on dates, I thought I’d throw some notes down about calculating the difference between some of them. Testing dataset Picking up from my article on\u00a0Dates In SQL Server: Create Sample Date Ranges\u00a0I’ll use a sample from there to create…<\/p>\n
Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[12],"class_list":["post-105","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-date"],"yoast_head":"\nDates In SQL Server: Adding & subtracting - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"Different DBMS systems have different ways of managing dates. Here are some examples for SQL Server.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dates In SQL Server: Adding & subtracting - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"Different DBMS systems have different ways of managing dates. Here are some examples for SQL Server.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-16T18:00:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-06-17T11:51:03+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Dates In SQL Server: Adding & subtracting\",\"datePublished\":\"2018-06-16T18:00:47+00:00\",\"dateModified\":\"2018-06-17T11:51:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/\"},\"wordCount\":287,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"date\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/\",\"name\":\"Dates In SQL Server: Adding & subtracting - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-06-16T18:00:47+00:00\",\"dateModified\":\"2018-06-17T11:51:03+00:00\",\"description\":\"Different DBMS systems have different ways of managing dates. Here are some examples for SQL Server.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/16\\\/dates-in-sql-server-adding-subtracting\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dates In SQL Server: Adding & subtracting\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dates In SQL Server: Adding & subtracting - Rows Across The Lake","description":"Different DBMS systems have different ways of managing dates. Here are some examples for SQL Server.","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:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/","og_locale":"en_GB","og_type":"article","og_title":"Dates In SQL Server: Adding & subtracting - Rows Across The Lake","og_description":"Different DBMS systems have different ways of managing dates. Here are some examples for SQL Server.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-06-16T18:00:47+00:00","article_modified_time":"2018-06-17T11:51:03+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Dates In SQL Server: Adding & subtracting","datePublished":"2018-06-16T18:00:47+00:00","dateModified":"2018-06-17T11:51:03+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/"},"wordCount":287,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["date"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/","name":"Dates In SQL Server: Adding & subtracting - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-06-16T18:00:47+00:00","dateModified":"2018-06-17T11:51:03+00:00","description":"Different DBMS systems have different ways of managing dates. Here are some examples for SQL Server.","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/16\/dates-in-sql-server-adding-subtracting\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Dates In SQL Server: Adding & subtracting"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/105","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=105"}],"version-history":[{"count":2,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/105\/revisions"}],"predecessor-version":[{"id":132,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/105\/revisions\/132"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}