[T-SQL]取得兩個日期間的差距，顯示成yy年mm月dd日格式

--好懂好讀版--

select cast(Y as varchar(1)) + 'Y'+cast(M as varchar(1)) +'M'+cast(D as varchar(1)) +'D' AS TimeDiff
from(
  select y, m, s, e,
    DATEDIFF(D, dateadd(MM, t.m, dateadd(YYYY, t.y, t.s)) , t.e) D
  from (
    select y, s, e, 
      case when DATEDIFF(D, DateAdd(MM, DATEDIFF(MM, dateadd(yyyy, t.y, t.s), t.e), dateadd(yyyy, t.y, t.s)), t.e) < 0 then DATEDIFF(MM, dateadd(yyyy, t.y, t.s), t.e)-1 else DATEDIFF(MM, dateadd(yyyy, t.y, t.s), t.e) end M
    from (
      SELECT case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end Y, s, e
      FROM (SELECT '20081231' 's', '20090101' 'e') t 
    ) t
  ) t
) t
;

-- 拆開來效能比較好版 --

select cast(Y as varchar(1)) + 'Y'+cast(M as varchar(1)) +'M'+cast(D as varchar(1)) +'D' AS TimeDiff
from(
SELECT case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end Y,
 case when DATEDIFF(D, DateAdd(MM, DATEDIFF(MM, dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s), t.e), dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s)), t.e) < 0 then DATEDIFF(MM, dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s), t.e)-1 else DATEDIFF(MM, dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s), t.e) end M,
 DATEDIFF(D, dateadd(MM, case when DATEDIFF(D, DateAdd(MM, DATEDIFF(MM, dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s), t.e), dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s)), t.e) < 0 then DATEDIFF(MM, dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s), t.e)-1 else DATEDIFF(MM, dateadd(yyyy, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s), t.e) end, dateadd(YYYY, case when DATEDIFF(D, DateAdd(YYYY, DATEDIFF(YYYY, t.s, t.e), t.s), t.e) < 0 then DATEDIFF(YYYY, t.s, t.e)-1 else DATEDIFF(YYYY, t.s, t.e) end, t.s)) , t.e) D
FROM (SELECT '20081231' 's', '20090101' 'e') t 
) t

[T-SQL] yyymm -> yyymmdd

-- YYYMM TO YYYMMDD (FIRST DAY)

select @STR+'01';

-- YYYMM TO YYYMMDD (LAST DAY)

select @STR+CONVERT(VARCHAR, DAY(DATEADD(DAY, -1, DATEADD(M, 1, convert(datetime, convert(varchar, (SUBSTRING(@str, 1, 3)+1911))+SUBSTRING(@str, 4, 2)+'01', 112)))));