in some scenario we comes in very strange requirement. with me it happens. i needed to calculate date in which fort night the date is\u00a0coming\u00a0and on the basis of that calculate start and end date. so i have created below script. I hope it will be useful to readers.<\/p>\n
\n\nDECLARE @InputDate DATETIME\nSET @InputDate = '17-Jul-2010'\n\nDECLARE @FortStartDate DATETIME\nDECLARE @FortEndDate DATETIME\n\nIF DAY(@InputDate) < 16 --First FortNight\nBEGIN\nSET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '\/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '\/01' AS DATETIME)\nSET @FortEndDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '\/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '\/15' AS DATETIME)\nEND\nELSE --Second FortNight\nBEGIN\nSET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '\/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '\/16' AS DATETIME)\nSET @FortEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))\nEND\n\nSELECT @FortStartDate as 'StartDate'\nSELECT @FortEndDate as 'EndDate'\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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.<\/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-501","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":501,"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 "SQL"","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":501,"position":1},"title":"SQL Server \u2013 Create Folders using T-SQL & 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 "SQL Server"","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":501,"position":2},"title":"SQL Server – 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 "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"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":501,"position":3},"title":"Get First Day and Last Day of Week – 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 "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"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":501,"position":4},"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 "SQL Server"","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":[]},{"id":5873,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/export-documents-saved-as-blob-binary-from-sql-server\/","url_meta":{"origin":501,"position":5},"title":"Export Documents saved as Blob \/ Binary from SQL Server","author":"Jitendra","date":"December 24, 2016","format":false,"excerpt":"T-SQL Scripts to Export Blob or Binary data stored in SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Export Blob as File","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/12\/SQL-Server-Export-Blob.gif?resize=700%2C400&ssl=1 2x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/501","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=501"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/501\/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=501"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=501"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=501"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}