December 21, 2010 at 3:21 pm
I have these parameters 1) @startdate = 08/25/2010 and 2) Total no of days = 149.
I want to know starting from this date and adding 149 days to this date, how many months it should be??
(e.g.) Answer should be 3 months and 21 days
December 21, 2010 at 3:43 pm
What logic do you want to apply to define a month?
Would it be based on the number of full month between the two dates or based on a fixed number (e.g. 30 days)? How would you deal with more than 12 month? Will it be 13 or one year one month?
Unfortunately, your sample data doesn't match your given result. I'd expect something like 4 month, 27 days.
What would be your expected output for @startdate = 01/01/2010 and total no of days = 60? Either 2 month 1 day or 1 month 33 day (or something different)?
December 21, 2010 at 3:47 pm
Thanks for the reply.
2 months and 1 day. and in terms of year 1 year 2 months and 3 days.
I want all days of the month not just work days.
December 21, 2010 at 4:00 pm
Examine the DATFDIFF and or DATEDIFF functions
You answer seems to be incorrect 149 days ... even if each month between 08/25/2010 and some future date contained 31 days it would be 4 months and 25 days NOT your required or given answer of 3 months and 21 days .... can you explain?
December 21, 2010 at 4:34 pm
Here's a rather complicated example:
DECLARE
@startdate DATETIME,
@days INT,
@enddate DATETIME,
@startyear SMALLINT,
@startmonth TINYINT,
@startday TINYINT,
@endyear SMALLINT,
@endmonth TINYINT,
@endday TINYINT
SELECT @days=149, @startdate='20100825'
SELECT @enddate=DATEADD(dd,@days,@startdate)
SELECT
@startyear =YEAR(@startdate),
@startmonth =MONTH(@startdate),
@startday =DAY(@startdate),
@endyear =YEAR(@enddate),
@endmonth =MONTH(@enddate),
@endday =DAY(@enddate)
SELECT
@startdate,
@enddate,
CASE
WHEN @startyear <> @endyear
THEN CAST(@endyear-@startyear AS VARCHAR(4))+' year(s) '
ELSE ''
END
+
CASE
WHEN @startyear*12+@startmonth <> @endyear*12+@endmonth
THEN CAST(((@endyear*12+@endmonth)-(@startyear*12+@startmonth))%12
+ CASE WHEN @startday>@endday THEN -1 ELSE 0 END
AS VARCHAR(2))+' month(s) '
ELSE ''
END
+
CAST(
CASE
WHEN @startday<=@endday
THEN @endday-@startday
ELSE
DATEDIFF(dd,@startdate,DATEADD(mm,DATEDIFF(mm,0,@startdate)+1,0))
+ DATEDIFF(dd,DATEADD(mm,DATEDIFF(mm,0,@enddate),0),@enddate)
END
AS VARCHAR(2)) + ' day(s)'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply