Labels

Wednesday, May 4, 2011

Month Days Calculation

SELECT CONVERT(decimal(10,1), datediff(DD, '1/1/2009','12/31/2009')/(365.0/12))

--OR

DECLARE @START_DATE DATETIME;
DECLARE @END_DATE DATETIME;
DECLARE @DIFF NVARCHAR(10);
declare @DIFF_IN_YEARS INT
declare @DIFF_IN_MONTHS  INT
declare @DIFF_IN_DAYS INT
SET @START_DATE = '8/1/2010';
SET @END_DATE = '12/22/2011';
select @DIFF_IN_YEARS = DIFF_IN_YEARS,
      @DIFF_IN_MONTHS = DIFF_IN_MONTHS,
      @DIFF_IN_DAYS = datediff(dd,dateadd(mm,DIFF_IN_MONTHS, dateadd(yy,DIFF_IN_YEARS,START_DATE)),END_DATE)
from
  (
  select
     DIFF_IN_MONTHS =   case
          when AnniversaryThisMonth <= END_DATE
          then datediff(mm,dateadd(yy,DIFF_IN_YEARS,START_DATE),END_DATE)
          else datediff(mm,dateadd(yy,DIFF_IN_YEARS,START_DATE),END_DATE)-1
          end,
     *
  from
   (
   select
      DIFF_IN_YEARS =
      case
      when AnniversaryThisYear <= END_DATE
      then datediff(yy,START_DATE,END_DATE)
      else datediff(yy,START_DATE,END_DATE)-1
      end,
      *
   from
    (
    select
       AnniversaryThisYear =
       dateadd(yy,datediff(yy,START_DATE,END_DATE),START_DATE),
       AnniversaryThisMonth =
       dateadd(mm,datediff(mm,START_DATE,END_DATE),START_DATE),
       *
    from
     (
     select START_DATE   = dateadd(dd,datediff(dd,0,@START_DATE),0),
        END_DATE     = dateadd(dd,datediff(dd,0,@END_DATE),0)
     ) aaaa
    ) aaa
   ) aa
 ) a
select @DIFF =
      right('00'+convert(varchar(4),@DIFF_IN_MONTHS),2)+'.'  +
      right(convert(varchar(4),Case
          WHEN (@DIFF_IN_DAYS/7)=1 THEN 2
          WHEN (@DIFF_IN_DAYS/7)=2 THEN 5
          WHEN (@DIFF_IN_DAYS/7)=3 THEN 7
          WHEN (@DIFF_IN_DAYS/7)=4 THEN 9
          ELSE (@DIFF_IN_DAYS/7)END) ,3)
     
SELECT @DIFF

No comments:

Post a Comment