getting start date and end date based on bimonthly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

getting start date and end date based on bimonthly

Author

Topic

gurmeetkalra
Starting Member

1 Post

Posted - 2014-06-07 : 03:26:53

I need a function in SQL Server that accepts any date and one string called frequency (either monthly or bimonthly), and based on the frequency and date passed it finally return the start and end date.

Scenario:

we have any date between 1 june to 15 june (any) lets say we pass 06/06/2014 and frequency is 'bimonthly'. Function will return 06/01/2014 and 06/15/2014.

Let suppose today is 16 Jun to 29 Jun (any) lets say 06/18/2014 and frequency is 'biomonthly', it gives me 2 dates
06/16/2014 to 06/30/2014.

Scenario 2: if the frequency is "Monthly" and the date passed is 06/20/2014 then the outcome will be 06/01/2014 to 06/30/2014. and if we set thefrequency as "monthly" and today's date is 06/28/2014. then it will return me 2 dates 06/01/2014 & 06/30/2014.

Regards
Gurmeet

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-07 : 04:01:00

[code]WITH cteMonth(theDate)
AS (
SELECT DATEADD(DAY, number, '20140601')
FROM master.dbo.spt_values
WHERE type = 'P'
AND number BETWEEN 0 AND 60
)
SELECT theDate,
CASE
WHEN DATEPART(DAY, theDate) >= 16 THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, theDate), 15)
ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, theDate), 0)
END AS FromDate,
CASE
WHEN DATEPART(DAY, theDate) >= 16 THEN DATEADD(MONTH, DATEDIFF(MONTH, -1, theDate), -1)
ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, theDate), 14)
END AS ToDate
FROM cteMonth
ORDER BY theDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-06-09 : 02:59:03

May be this will help you ..


DECLARE @Date date = '06/08/2014'
DECLARE @Frequency varchar(max) = 'bimonthly'
IF ((DAY(@DATE)<=15) AND @Frequency = 'bimonthly')

	 SELECT DATEADD(dd,-DAY(@Date)+1,@Date) AS StartDate,DATEADD(dd,-DAY(@Date)+15,@Date) AS EndDate 

ELSE IF ((DAY(@DATE)>15) AND @Frequency = 'bimonthly')

	 SELECT DATEADD(dd,-DAY(@Date)+16,@Date),DATEADD(DD,-DAY(DATEADD(MONTH,1,@DATE)),DATEADD(MONTH,1,@DATE)) AS EndDate

ELSE IF ((DAY(@DATE)<=15) AND @Frequency = 'Monthly')
	BEGIN
			;WITH Calender
			AS (   SELECT   DATEADD(dd,-DAY(@Date)+1,@Date) AS [Date]
		           UNION    ALL
		           SELECT   DATEADD(DD,1,[Date]) FROM  Calender 
		           WHERE    DATEADD(DD,1,[Date]) <= DATEADD(DD,-DAY(DATEADD(MONTH,1,@DATE)),DATEADD(MONTH,1,@DATE))
		)
		 SELECT * FROM  Calender
	END

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

rocknpop
Posting Yak Master

201 Posts

Posted - 2014-06-18 : 05:18:53

Another one:

DECLARE @DT DATETIME ='20140318', @freq BIT = 0 --0==BIMONTHLY,1=MONTHLY

;WITH CTE
AS
(
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0, @DT),0) AS startDate,
DATEADD(DAY,14, DATEADD(MONTH,DATEDIFF(MONTH,0, @DT),0)) AS endDate,
1 AS iType

UNION

SELECT DATEADD(DAY,15, DATEADD(MONTH,DATEDIFF(MONTH,0, @DT),0)),
DATEADD(DAY,-1, DATEADD(MONTH,DATEDIFF(MONTH,0, @DT)+1,0)),
2 AS iType

UNION

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0, @DT),0),
DATEADD(DAY,-1, DATEADD(MONTH,DATEDIFF(MONTH,0, @DT)+1,0)),
3 AS iType
)

SELECT * FROM CTE
where iType = CASE
WHEN @freq=0 AND DATEPART(DAY,@DT)<=15 THEN 1
WHEN @freq=0 AND DATEPART(DAY,@DT)>15 THEN 2
WHEN @freq=1 THEN 3
END

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources