need help in stored procedure for calendar days

Question

question

adishri22 asked Oct 8, '15

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-2008 sql-server-2008-r2 stored-procedures calendar

11

10 |1200

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

Kev Riley ♦♦ commented · Oct 07, 2015 at 01:03 PM

How do you define 'calendar weeks'?

0 ·

adishri22 commented · Oct 07, 2015 at 01:32 PM

sorry I didn't see the comment but my weeks starts from Monday

0 ·

Kev Riley ♦♦ commented · Oct 07, 2015 at 01:42 PM

so between Wednesday 2 Sept 2015 and Friday 11 Sept there are how many calendar weeks? 1 or 0 ?

0 ·

adishri22 commented · Oct 07, 2015 at 01:46 PM

there is no complete week so it will be 0

0 ·

Kev Riley ♦♦ commented · Oct 07, 2015 at 01:53 PM

OK I think I get it now

0 ·

Show more comments

Answer 1 · 2015-10-07T13:26:57Z

Kev Riley answered Oct 7, '15

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

10 |1200

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

Kev Riley ♦♦ commented · Oct 07, 2015 at 01:36 PM

test first!

1 ·

adishri22 commented · Oct 07, 2015 at 01:33 PM

where do I add this code...do I have to replace it or add in my original one?

0 ·

adishri22 commented · Oct 07, 2015 at 01:37 PM

yeah but where to place these lines!

0 ·

Kev Riley ♦♦ commented · Oct 07, 2015 at 01:39 PM

if it does what you want, then this code would replace the body of the stored procedure

0 ·

adishri22 commented · Oct 07, 2015 at 01:43 PM

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 ·

Show more comments

Answer 2 · 2015-10-13T11:48:12Z

adishri22 answered Oct 13, '15

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

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

question