| Author |
 Topic  |
|
|
gurmeetkalra
Starting Member
1 Posts |
 Posted - 06/07/2014 : 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
Sweden
30114 Posts |
Posted - 06/07/2014 : 04:01:00
|
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 |
 |
|
|
MuralikrishnaVeera
Posting Yak Master
India
106 Posts |
Posted - 06/09/2014 : 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
184 Posts |
Posted - 06/18/2014 : 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 |
|
|
| |
Topic |
|
|
|
getting start date and end date based on bimonthly
