{"id":3360,"date":"2020-07-12T23:08:50","date_gmt":"2020-07-12T21:08:50","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3360"},"modified":"2020-07-12T23:08:50","modified_gmt":"2020-07-12T21:08:50","slug":"dates-in-sql-server","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3360","title":{"rendered":"Dates in SQL server"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">SQL Server is has several possibilities to calculate elapsed time (such as age).  Two approaches are possible. The first idea is to calculate the number of hours and divide this by the number of hours in a year. The other possibility is to make a distinction between the number of a day in a month as compared between the first date and the last date.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let me first declare a first date. Let us think of a date of birth.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DECLARE @dob date<br>SET @dob='2001-01-22'<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Then the last date is declared.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DECLARE @ref date<br>SET @ref='2021-01-21'<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Another last date is declared whereby the first day in a month is taken.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DECLARE @ref1 date<br>SET @ref1=convert(date,convert(char(8),DATEPART(YEAR,@ref)<em>10000+datepart(month,@ref)<\/em>100+1),112)<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The first calculation provides an age.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">SELECT floor(DATEDIFF(hour,@dob,@ref)\/8765.82) AS AgeYears<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The second calculation provides another calculation of an age.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select CASE WHEN DATEPART(DAY, @ref) &lt; DATEPART(DAY ,@dob)<br>THEN DATEDIFF(YEAR, @dob, @ref) - 1<br>ELSE DATEDIFF(YEAR, @dob, @ref)<br>END as \"Leeftijd\"<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Then the difference is calculated between the date of birth and a first day in a month.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select DATEDIFF(YEAR, @dob, @ref1) as \"Leeftijd_Eerste_Maand\"<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Server is has several possibilities to calculate elapsed time (such as age). Two approaches are possible. The first idea is to calculate the number of hours and divide this by the number of hours in a year. The other possibility is to make a distinction between the number of a day in a month [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3360","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3360","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3360"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3360\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3360"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3360"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3360"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}