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'
Leave a Reply