{"id":5847,"date":"2013-05-30T11:30:52","date_gmt":"2013-05-30T01:30:52","guid":{"rendered":"http:\/\/mingersoft.com\/blog\/?p=5847"},"modified":"2013-05-29T20:43:42","modified_gmt":"2013-05-29T10:43:42","slug":"handy-sql-code-snippets","status":"publish","type":"post","link":"https:\/\/mingersoft.com\/blog\/2013\/05\/handy-sql-code-snippets\/","title":{"rendered":"Handy SQL Code Snippets"},"content":{"rendered":"<p>I admit that I can be a bit lazy with my SQL coding and sometimes I do borrow handy snippets to get the job done. I should probably have memorised some of these or at least turned them into functions that can be called when required but I&#8217;ve not managed that yet (on the list of things to do).<\/p>\n<p><span style=\"font-size: 12px; line-height: 18px;\">Unfortunately, one of my favourite sites for date code snippets no longer keeps the page for T-SQL (i.e. Microsoft SQL Server) code for a raft of various relative date calculations so I am going to list them here for others who might be after them (and also myself).<\/span><\/p>\n<p>Ironically, if I had turned some of these into functions I wouldn&#8217;t be in this predicament \ud83d\ude42<\/p>\n<p>Anyway, here is what used to be @\u00a0http:\/\/shanecooper.net\/cooldatefunctions.htm<\/p>\n<p><strong>First Day of Month<br \/>\n<\/strong>select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)<\/p>\n<p><strong>First Day of Last Month<\/strong><br \/>\nselect DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)<\/p>\n<p><strong>Monday of the Current Week<\/strong><br \/>\nselect DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)<\/p>\n<p><strong>Sunday of the Current Week<\/strong><br \/>\nset DATEFIRST 1<br \/>\n<em id=\"__mceDel\" style=\"font-size: 12px; line-height: 18px;\">select DATEADD(dd, 1 &#8211; DATEPART(dw, getdate()), getdate())<\/em><\/p>\n<p><strong>First Day of the Year<\/strong><br \/>\nselect DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)<\/p>\n<p><strong>First Day of the Quarter<\/strong><br \/>\nselect DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)<\/p>\n<p><strong>Midnight for the Current Day<\/strong><br \/>\nselect DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)<\/p>\n<p><strong>Last Day of Prior Month<\/strong><br \/>\nselect dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))<\/p>\n<p><strong>Last Day of Prior Year<\/strong><br \/>\nselect dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))<\/p>\n<p><strong>Last Day of Current Month<\/strong><br \/>\nselect dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))<\/p>\n<p><strong>Last Day of Current Year<\/strong><br \/>\nselect dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))<\/p>\n<p><strong>First Monday of the Month<\/strong><br \/>\nselect DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I admit that I can be a bit lazy with my SQL coding and sometimes I do borrow handy snippets to get the job done. I should probably have memorised some of these or at least turned them into functions that can be called when required but I&#8217;ve not managed that yet (on the list &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/mingersoft.com\/blog\/2013\/05\/handy-sql-code-snippets\/\">Continue reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":4214,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[18],"tags":[200],"class_list":["post-5847","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology","tag-sql","item-wrap"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/mingersoft.com\/blog\/wp-content\/uploads\/2012\/05\/SQL-Server-Logo.png?fit=247%2C267&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/posts\/5847","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/comments?post=5847"}],"version-history":[{"count":0,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/posts\/5847\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/media\/4214"}],"wp:attachment":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/media?parent=5847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/categories?post=5847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/tags?post=5847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}