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
--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