question

adishri22 avatar image
adishri22 asked

need help in stored procedure for calendar days

I have to find the total no of month, days or month, weeks or weeks days or only days on the basis of interval between start and end date which user gives the input. Like if `the interval between two dates is not more than 30` than output should be only in days` For example- StartDate - 1 July EndDate-24 July %|-1678747367_1|% %|-1541804783_2|% Interval is more than 30 days but less than 90 Days` than output should only in days and weeks and my week start from Monday and ends on Sunday For example- StartDate - 1 July 2015 EndDate-23 September 2015 Time_Type StartDate EndDate Day 1-07-2015 5-07-2015 Week Week2(july) Week3(September) Day 21-09-2015 23-09-2015 `Interval is more than 90 Days than` output should be in days and months For example- StartDate – 22 April 2015 EndDate-8 December 2015 Time_Type StartDate EndDate Day 22-04-2015 30-04-2015 Week May2015 November 2015 Day 01-12-2015 08-12-2015 Here the weeks can be the no of week by year wise like week2 (July) would be 26 week.This is needed in a project and its urgent also please do help me!!! Thank you! this is for getting date when interval is less than 30 days SELECT DATENAME(DAY,GETDATE()) + ' ' + DATENAME(MONTH,GETDATE()) this is for getting date when interval is more than 30 days less than 90 days SELECT 'Week ' + CAST(DATEPART(WEEK,GETDATE()) - DATEPART(WEEK,DATENAME(YEAR,GETDATE()) + '/' + DATENAME(MONTH,GETDATE()) + '/' + '1') AS NVARCHAR)+ ' ' + DATENAME(MONTH,GETDATE()) this is for getting date when interval is more than 90 days SELECT DATENAME(MONTH,GETDATE()) + ' ' + DATENAME(YEAR,GETDATE())
sql-server-2008sql-server-2008-r2stored-procedurescalendar
11 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

How do you define 'calendar weeks'?
0 Likes 0 ·
sorry I didn't see the comment but my weeks starts from Monday
0 Likes 0 ·
so between Wednesday 2 Sept 2015 and Friday 11 Sept there are how many calendar weeks? 1 or 0 ?
0 Likes 0 ·
there is no complete week so it will be 0
0 Likes 0 ·
OK I think I get it now
0 Likes 0 ·
Show more comments
Kev Riley avatar image
Kev Riley answered
Not sure about 'calendar weeks' yet as we are waiting on the definition, but here's a quicker way of doing what I think you are asking for declare @StartDate DATE , @EndDate date set @StartDate = '3 jun 2015' set @EndDate = '4 aug 2015' select datediff(month, dateadd (day, -datepart(day, @StartDate)+1, @StartDate), dateadd (day, -datepart(day, @EndDate)+1, @EndDate) ) - case when datepart(day, @StartDate) > datepart(day, @EndDate) then 1 else 0 end as 'Months', datediff(day, dateadd(month, datediff(month, dateadd (day, -datepart(day, @StartDate)+1, @StartDate), dateadd (day, -datepart(day, @EndDate)+1, @EndDate) ) - case when datepart(day, @StartDate) > datepart(day, @EndDate) then 1 else 0 end, @StartDate) , @EndDate) as 'days'
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

where do I add this code...do I have to replace it or add in my original one?
0 Likes 0 ·
yeah but where to place these lines!
0 Likes 0 ·
if it does what you want, then this code would replace the body of the stored procedure
0 Likes 0 ·
actually for the date you have given me it is showing 2 months but we does not have full month of june so it should show only 1 month 3 weeks and 13 days something like this
0 Likes 0 ·
Show more comments
adishri22 avatar image
adishri22 answered
IF OBJECT_ID('IAS_TEST_SCHEMA.DATESPLIT', 'P') IS NOT NULL DROP PROCEDURE IAS_TEST_SCHEMA.DATESPLIT; GO CREATE PROCEDURE IAS_TEST_SCHEMA.DATESPLIT (@STARTDATE date, @ENDDATE date) AS BEGIN DECLARE @DATEDIFF integer, @MONTHEND integer = 0, @MONTHSTART integer = 0, @STARTWEEK integer = 0, @ENDWEEK integer = 0; IF @STARTDATE = CONVERT(date, DATEADD(M, DATEDIFF(M, 0, @STARTDATE), 0)) BEGIN SET @MONTHSTART = 1; END IF MONTH(@ENDDATE) != MONTH(DATEADD(DAY, 1, @ENDDATE)) BEGIN SET @MONTHEND = 1; END IF DATEPART(DW, @STARTDATE) = 2 BEGIN SET @STARTWEEK = 1; END IF DATEPART(DW, @ENDDATE) = 1 BEGIN SET @ENDWEEK = 1; END SET @DATEDIFF = DATEDIFF(DAY, @STARTDATE, @ENDDATE); SELECT CASE WHEN @DATEDIFF <= 30 THEN CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) WHEN @DATEDIFF > 30 AND @DATEDIFF < 90 THEN CASE WHEN @STARTWEEK = 1 THEN 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) ELSE CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) END WHEN @DATEDIFF >= 90 THEN CASE WHEN @MONTHSTART = 1 THEN DATENAME(MONTH, @STARTDATE) + ' ' + DATENAME(YEAR, @STARTDATE) ELSE CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) END END AS START_DATE, CASE WHEN @DATEDIFF <= 30 THEN CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) WHEN @DATEDIFF > 30 AND @DATEDIFF < 90 THEN CASE WHEN @STARTWEEK = 1 THEN CASE WHEN @ENDWEEK = 1 THEN 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) ELSE 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) END ELSE CONVERT(varchar, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @STARTDATE), 6))) END WHEN @DATEDIFF >= 90 THEN CASE WHEN @MONTHSTART = 1 THEN CASE WHEN @MONTHEND = 0 THEN DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, -1, @ENDDATE) - 1, -1)) + ' ' + DATENAME(YEAR, DATEADD(MONTH, DATEDIFF(MONTH, -1, @ENDDATE) - 1, -1)) ELSE DATENAME(MONTH, @ENDDATE) + ' ' + DATENAME(YEAR, @ENDDATE) END ELSE CONVERT(varchar, CONVERT(date, DATEADD(S, -1, DATEADD(MM, DATEDIFF(M, 0, @STARTDATE) + 1, 0)))) END END AS END_DATE UNION SELECT CASE WHEN @DATEDIFF <= 30 THEN CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) WHEN @DATEDIFF > 30 AND @DATEDIFF < 90 THEN CASE WHEN @STARTWEEK = 1 THEN 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) ELSE 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) END WHEN @DATEDIFF >= 90 THEN CASE WHEN @MONTHSTART = 1 THEN DATENAME(MONTH, @STARTDATE) + ' ' + DATENAME(YEAR, @STARTDATE) ELSE DATENAME(MONTH, CONVERT(date, DATEADD(M, DATEDIFF(M, -1, @STARTDATE), 0))) + ' ' + DATENAME(YEAR, CONVERT(date, DATEADD(M, DATEDIFF(M, -1, @STARTDATE), 0))) END END, CASE WHEN @DATEDIFF <= 30 THEN CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) WHEN @DATEDIFF > 30 AND @DATEDIFF < 90 THEN CASE WHEN @ENDWEEK = 1 THEN 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) ELSE 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -1))) END WHEN @DATEDIFF > 90 THEN CASE WHEN @MONTHEND = 0 THEN DATENAME(MONTH, CONVERT(date, DATEADD(MONTH, DATEDIFF(MONTH, -1, @ENDDATE) - 1, -1))) + ' ' + DATENAME(YEAR, CONVERT(date, DATEADD(MONTH, DATEDIFF(MONTH, -1, @ENDDATE) - 1, -1))) ELSE DATENAME(MONTH, @ENDDATE) + ' ' + DATENAME(YEAR, @ENDDATE) END END UNION SELECT CASE WHEN @DATEDIFF <= 30 THEN CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) WHEN @DATEDIFF > 30 AND @DATEDIFF < 90 THEN CASE WHEN @ENDWEEK = 1 THEN CASE WHEN @STARTWEEK = 1 THEN 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 0))) ELSE 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @STARTDATE), 7))) END ELSE CONVERT(varchar, CONVERT(date, DATEADD(WEEK, DATEDIFF(WEEK, 0, @ENDDATE), -0))) END WHEN @DATEDIFF >= 90 THEN CASE WHEN @MONTHEND = 1 THEN DATENAME(MONTH, @STARTDATE) + ' ' + DATENAME(YEAR, @STARTDATE) ELSE CONVERT(varchar, CONVERT(date, DATEADD(DD, -(DAY(@ENDDATE) - 1), @ENDDATE))) END END, CASE WHEN @DATEDIFF <= 30 THEN CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) WHEN @DATEDIFF > 30 AND @DATEDIFF < 90 THEN CASE WHEN @ENDWEEK = 1 THEN 'WEEK ' + CAST(DATEPART(WEEK, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) - DATEPART(WEEK, DATENAME(YEAR, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) + '/' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) + '/' + '1') AS nvarchar) + ' ' + DATENAME(MONTH, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) ELSE CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) END WHEN @DATEDIFF >= 90 THEN CASE WHEN @MONTHEND = 1 THEN DATENAME(MONTH, @ENDDATE) + ' ' + DATENAME(YEAR, @ENDDATE) ELSE CONVERT(varchar, CONVERT(date, DATEADD(DAY, DATEDIFF(DAY, 0, @ENDDATE), 0))) END END END; GO
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.