SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 getting start date and end date based on bimonthly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gurmeetkalra
Starting Member

1 Posts

Posted - 06/07/2014 :  03:26:53  Show Profile  Reply with Quote
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

Sweden
30241 Posts

Posted - 06/07/2014 :  04:01:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 06/09/2014 :  02:59:03  Show Profile  Reply with Quote
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.......
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 06/18/2014 :  05:18:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000