{"id":492,"date":"2010-07-02T17:56:09","date_gmt":"2010-07-02T12:26:09","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=492"},"modified":"2010-07-02T17:56:09","modified_gmt":"2010-07-02T12:26:09","slug":"get-first-day-and-last-day-of-month-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-month-sql-server\/","title":{"rendered":"Get First Day and Last Day of Month \u2013 SQL Server"},"content":{"rendered":"<p>One of the features i love about SQL Server is function supports. Divide your task in functions just like other programming language. In this article i am going to show you that how to display the First Day and Last Day of Month in SQL Server.<\/p>\n<p>Create a below function to get the First Day of Month:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE FUNCTION GetFirstDay\n(\n-- Add the parameters for the function here\n@InputDate DateTime\n)\nRETURNS DateTime\nAS\nBEGIN\n-- Declare the return variable here\nDECLARE @Result DateTime\n\n-- Add the T-SQL statements to compute the return value here\nSELECT @Result = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '\/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '\/01' AS DATETIME)\n\n-- Return the result of the function\nRETURN @Result\n\nEND\nGO\n<\/pre>\n<p>Now, Create Below Function to get the Last day of month.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE FUNCTION GetLastDay\n(\n-- Add the parameters for the function here\n@InputDate DateTime\n)\nRETURNS DateTime\nAS\nBEGIN\n-- Declare the return variable here\nDECLARE @Result DateTime\n\n-- Add the T-SQL statements to compute the return value here\nSELECT @Result = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))\n\n-- Return the result of the function\nRETURN @Result\n\nEND\nGO\n<\/pre>\n<p>To get the values from function, run below script<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nselect dbo.GetFirstDay(GETDATE()) as StartDate\nselect dbo.GetLastDay(GETDATE()) as EndDate\n<\/pre>\n<p>remember that to call function we have to call like <strong>dbo<\/strong>.functionName<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Get the first day and last day of the month in SQL Server using Scalar functions<\/p>\n","protected":false},"author":1,"featured_media":455,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[13,14],"tags":[333,292],"class_list":["post-492","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","category-sqlserver","tag-sql","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":501,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-the-fortnight-start-and-end-date-sql-server\/","url_meta":{"origin":492,"position":0},"title":"Get the FortNight Start and End date \u2013 SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Get the Fort night start and end date on the basis of input provided. if date falls in first 15 day then it will give the First Fort night start and end date and if date falls after 15 day then it gives respective start and end date.","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":498,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-week-sql-server\/","url_meta":{"origin":492,"position":1},"title":"Get First Day and Last Day of Week &#8211; SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Scalar function to get the first Day and Last day of the Week in SQL Server","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5824,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-create-folders-using-t-sql-ole-automation\/","url_meta":{"origin":492,"position":2},"title":"SQL Server \u2013 Create Folders using T-SQL &#038; OLE automation","author":"Jitendra","date":"November 24, 2016","format":false,"excerpt":"Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server \u2013 Create Folders using T-SQL & OLE automation","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":1147,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/between-clause-problem-in-date-comparison-sql-server\/","url_meta":{"origin":492,"position":3},"title":"Between Clause problem in Date comparison &#8211; SQL Server","author":"Jitendra","date":"September 23, 2010","format":false,"excerpt":"SQL Server Date Comparison Tips : Never user Between clause with DateTime in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":492,"position":4},"title":"SQL Server &#8211; Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1376,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/use-of-openxml-in-sql-server\/","url_meta":{"origin":492,"position":5},"title":"Use of OPENXML in SQL Server","author":"Jitendra","date":"December 4, 2010","format":false,"excerpt":"In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string.","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"OpenXML in SQL Server","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/OpenXML-in-SQL-Server.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/492","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=492"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/492\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=492"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}