Get the FortNight Start and End date – SQL Server

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 coming and on the basis of that calculate start and end date. so i have created below script. I hope it will be useful to readers.


DECLARE @InputDate DATETIME
SET @InputDate = '17-Jul-2010'

DECLARE @FortStartDate DATETIME
DECLARE @FortEndDate DATETIME

IF DAY(@InputDate) < 16 --First FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
SET @FortEndDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/15' AS DATETIME)
END
ELSE --Second FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/16' AS DATETIME)
SET @FortEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))
END

SELECT @FortStartDate as 'StartDate'
SELECT @FortEndDate as 'EndDate'

3 comments

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.