sql獲取日期區間（年、月、周、日）

通過存儲過程獲取當前日期（或指定日期）所在的日期區間，包含年、月、周、日。

CREATE PROCEDURE [dbo].[GetDataPeriod]
    @defaultDate DATE=NULL,
    @period VARCHAR(1)=NULL,
    @startDate DATE OUTPUT,
    @endData DATE OUTPUT
AS
BEGIN
    IF    @defaultDate IS NULL
    BEGIN
        SET @defaultDate=GETDATE();
    END
    IF    @period IS NULL
    BEGIN
        SET @period='M';
    END
    --第一天
    SELECT @startDate=
        CASE @period
        WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR,0,@defaultDate), 0)
        WHEN 'M' THEN DATEADD(MONTH, DATEDIFF(MONTH,0,@defaultDate), 0)
        WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK,0,@defaultDate), 0)
        WHEN 'D' THEN DATEADD(DAY, DATEDIFF(DAY,0,@defaultDate), 0)
        END;
    IF @defaultDate<@startDate
    BEGIN
        SET @defaultDate=DATEADD(DAY,-1,@defaultDate);
        EXEC dbo.Report_GetPeriod @defaultDate,@period,@startDate OUTPUT,@endData OUTPUT;
    END
    ELSE
    BEGIN
    --最後一天
    SELECT @endData=
        CASE @period
        WHEN 'Y' THEN DATEADD(DAY,-1,DATEADD(YEAR,1,DATEADD(YEAR, DATEDIFF(YEAR,0,@defaultDate), 0)))
        WHEN 'M' THEN DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(MONTH, DATEDIFF(MONTH,0,@defaultDate), 0)))
        WHEN 'W' THEN DATEADD(DAY,-1,DATEADD(WEEK,1,DATEADD(WEEK, DATEDIFF(WEEK,0,@defaultDate), 0)))
        WHEN 'D' THEN DATEADD(DAY, DATEDIFF(DAY,0,@defaultDate), 0)
        END;
    END
END

使用實例

DECLARE @startDate DATE,
    @endData DATE;
EXEC dbo.GetDataPeriod @defaultDate = '2017-06-20',
    @period = 'Y',
    @startDate = @startDate OUTPUT,
    @endData = @endData OUTPUT
SELECT @startDate,@endData;--// 2017-01-01 2017-12-31
 
EXEC dbo.GetDataPeriod @defaultDate = '2017-06-20',
    @period = 'M',
    @startDate = @startDate OUTPUT,
    @endData = @endData OUTPUT
SELECT @startDate,@endData;--// 2017-06-01 2017-06-30
 
EXEC dbo.GetDataPeriod @defaultDate = '2017-06-20',
    @period = 'W',
    @startDate = @startDate OUTPUT,
    @endData = @endData OUTPUT
SELECT @startDate,@endData;--// 2017-06-19 2017-06-25
 
EXEC dbo.GetDataPeriod @defaultDate = '2017-06-20',
    @period = 'D',
    @startDate = @startDate OUTPUT,
    @endData = @endData OUTPUT
SELECT @startDate,@endData;--// 2017-06-20 2017-06-20