SQL Server常用日期计算

获取本周、本月、本季、本年的第一天和最后一天
YTD|MTD|WTD|LMD|LWD计算

DECLARE @today datetime
SET @today = GETDATE()
-- TRAGET DATE
-- [本年第一天,本年最后一天]
SELECT 'YTD' AS CATEGORY_TIME
    ,@today AS CURRENT_DAY
    ,DATEADD(year, -1, @today) AS LAST_CURRENT_DAY -- 同期
    ,DATEADD(year, DATEDIFF(year, 0, @today), 0)  AS START_DAY 
    ,DATEADD(year, DATEDIFF(year, -1, @today), -1)  AS END_DAY
UNION ALL 
--[本月第一天,本月最后一天]
SELECT 'MTD' AS CATEGORY_TIME
    ,@today AS CURRENT_DAY
    ,DATEADD(month, -1, @today)  AS LAST_CURRENT_DAY -- 同期
    ,DATEADD(month, DATEDIFF(month, 0, @today), 0)  AS START_DAY 
    ,DATEADD(month, DATEDIFF(month, -1, @today), -1)  AS END_DAY
UNION ALL 
-- [本周第一天,本周最后一天]
SELECT 'WTD' AS CATEGORY_TIME
    ,@today AS CURRENT_DAY
    ,DATEADD(day, -7, @today)  AS LAST_CURRENT_DAY -- 同期
    ,DATEADD(day,-(DATEPART(weekday,@today)-1),@today) AS START_DAY 
    ,DATEADD(day,-(DATEPART(weekday,@today)-1)+6,@today)  AS END_DAY
UNION ALL 
-- [上月第一天,上月最后一天]
SELECT 'LMD' AS CATEGORY_TIME
    ,@today AS CURRENT_DAY
    ,DATEADD(month, -1, @today)  AS LAST_CURRENT_DAY -- 同期
    ,DATEADD(month, DATEDIFF(month, 0, DATEADD(month, -1,@today)), 0) AS START_DAY 
    ,DATEADD(month, DATEDIFF(month, -1, DATEADD(month, -1,@today)), -1) AS END_DAY
UNION ALL 
-- [上周第一天,上周最后一天]
SELECT 'LWD' AS CATEGORY_TIME
    ,@today AS CURRENT_DAY
    ,DATEADD(day, -7, @today)  AS LAST_CURRENT_DAY -- 同期
    ,DATEADD(day,-(DATEPART(weekday,DATEADD(ww, -1,@today))-1),DATEADD(ww, -1,@today)) AS START_DAY 
    ,DATEADD(day,-(DATEPART(weekday,DATEADD(ww, -1,@today))-1)+6,DATEADD(ww, -1,@today)) AS END_DAY