{"id":498,"date":"2010-07-02T18:09:34","date_gmt":"2010-07-02T12:39:34","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=498"},"modified":"2010-07-02T18:09:34","modified_gmt":"2010-07-02T12:39:34","slug":"get-first-day-and-last-day-of-week-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-week-sql-server\/","title":{"rendered":"Get First Day and Last Day of Week &#8211; SQL Server"},"content":{"rendered":"<p>This time lets create the Scalar function in SQL Server to get the First Day and Last Day of the Week.<\/p>\n<p>Create below Scalar function in SQL Server to get the First Day of Week.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE FUNCTION GetFirstDayWeek\n(\n\t-- Add the parameters for the function here\n\t@InputDate DateTime\n)\nRETURNS DateTime\nAS\nBEGIN\n\t-- Declare the return variable here\n\tDECLARE @Result DateTime\n\n\t-- Add the T-SQL statements to compute the return value here\n\t-- 1 -&gt; Sunday, 7 -&gt; Saturday\n\tSELECT @Result = DATEADD(DAY, 1- DATEPART(DW, @InputDate), @InputDate)\n\n\t-- Return the result of the function\n\tRETURN @Result\n\nEND\nGO\n<\/pre>\n<p>Create below Scalar function in SQL Server to get the Last Day of Week.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n\nCREATE FUNCTION GetLastDayWeek\n(\n\t-- Add the parameters for the function here\n\t@InputDate DateTime\n)\nRETURNS DateTime\nAS\nBEGIN\n\t-- Declare the return variable here\n\tDECLARE @Result DateTime\n\n\t-- Add the T-SQL statements to compute the return value here\n\t-- 1 -&gt; Sunday, 7 -&gt; Saturday\n\tSELECT @Result = DATEADD(DAY, 7- DATEPART(DW, @InputDate), @InputDate)\n\n\t-- Return the result of the function\n\tRETURN @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=\"\">\n select dbo.GetFirstDayWeek(GETDATE()) as StartDate\n select dbo.GetLastDayWeek(GETDATE()) as EndDate\n<\/pre>\n<p>as i said, remember that to call function we have to call like <strong>dbo<\/strong>.functionName<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Scalar function to get the first Day and Last day of the Week in SQL Server<\/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-498","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":492,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-month-sql-server\/","url_meta":{"origin":498,"position":0},"title":"Get First Day and Last Day of Month \u2013 SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Get the first day and last day of the month in SQL Server using Scalar functions","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":504,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/drop-function-if-exist-sql-server\/","url_meta":{"origin":498,"position":1},"title":"Drop Function if exist &#8211; SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Drop the user define function from database if it exists.","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":501,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-the-fortnight-start-and-end-date-sql-server\/","url_meta":{"origin":498,"position":2},"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":1147,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/between-clause-problem-in-date-comparison-sql-server\/","url_meta":{"origin":498,"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":5824,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-create-folders-using-t-sql-ole-automation\/","url_meta":{"origin":498,"position":4},"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":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":498,"position":5},"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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/498","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=498"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/498\/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=498"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=498"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=498"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}