SQL Server: DateTime Range Pro

DiponRoy

3.59/5 (8 votes)

11 Jul 2019CPOL2 min read

9.9K

137

Populating DateTime range in SQL Server

Download SQL code file - 2.5 KB

Introduction

There is a common need in reporting to list/return data that is tagged based on date time attributes. These may include creating various reports like daily, weekly, monthly, yearly, etc. This article will especially help those people who need to generate various date ranges. The article and examples focused on DATETIME data type.

Background

What are we going to do?

Find start and end date time of a day
Populate days with a range
Find start and end date time of a week
Populate weeks with a range
Find start and end date time of a month
Populate months with a range
Find start and end date time of a year
Populate years with a range
Few other DateTime related things

Day

Day Range

Finding start and end date time of a date.

SQL

DECLARE @dateTimeNow DATETIME ='2019-07-01 17:20:00'    /*yyyy-MM-dd HH:mm:ss*/
--DECLARE @dateTimeNow DATETIME = GETDATE();            /*now*/

SELECT 
    [StartDateTime] = DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0),
    [EndDateTime] = DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0));

Populate Days With Range

Generating a date list with a daily range:

SQL

DECLARE @startDateTime DATETIME ='2019-09-21', _
           @endDateTime DATETIME ='2019-09-30';    /*yyyy-MM-dd*/
--SET @startDateTime = GETDATE(); _
           SET @endDateTime = @startDateTime + 10;                /*now*/

WITH Dates([Date])
AS 
(
    SELECT [Date]= @startDateTime 
         UNION ALL
         SELECT [Date] + 1 
             FROM   Dates 
             WHERE  [Date] + 1 <= @endDateTime
), 
DateRange([Date], [StartDateTime], [EndDateTime])
AS
(
    SELECT 
        [Date],
        DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0),
        DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, [Date]) + 1, 0))
        FROM Dates 
)
SELECT * 
    FROM   DateRange 
    OPTION (MAXRECURSION 0)

Week

Week Range

Finding start and end date time of a week.

SQL

DECLARE @dateTimeNow DATETIME ='2019-07-01'    /*yyyy-MM-dd*/
--DECLARE @dateTimeNow DATETIME = GETDATE();    /*now*/

SELECT 
    [StartDate] = DATEADD(dd, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                  DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0)),
    [EndDate] = DATEADD(dd, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
                DATEADD(dd, DATEDIFF(dd, 0, @dateTimeNow), 0));
SELECT 
    [StartDateTime] = DATEADD(DAY, -(DATEPART(WEEKDAY, @dateTimeNow)-1), _
                      DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow), 0)),
    [EndDateTime] = DATEADD(DAY, 7-(DATEPART(WEEKDAY, @dateTimeNow)), _
                    DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @dateTimeNow) + 1, 0)))

Populate Weeks With Range

Generating a week list with a weekly range:

SQL

DECLARE @startDateTime DATETIME = '2019-04-01 03:20:00', _
        @endDateTime DATETIME = '2019-04-30 03:20:00';

WITH Weeks([Date])
AS 
(
    SELECT [Date] = @startDateTime 
         UNION ALL
         SELECT DATEADD(DAY, 7, [Date]) 
             FROM   Weeks 
             WHERE  DATEADD(DAY, 7, [Date]) <= @endDateTime
),
WeekRange([Date], [StartDateTime], [EndDateTime])
AS
(
    SELECT 
        [Date],
        DATEADD(DAY, -(DATEPART(WEEKDAY, [Date])-1), _
                DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0)),
        DATEADD(DAY, 7-(DATEPART(WEEKDAY, [Date])), _
                DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, [Date]) + 1, 0)))
        FROM Weeks 
)
SELECT * 
    FROM   WeekRange 
    OPTION (MAXRECURSION 0)

Change Week Start Day

In SQL Server, Sunday is default week start day. There is a @@DATEFIRST function, which returns the current week start day (value of SET DATEFIRST). To change default week start day, we can set any week start day value between 1-7 to DATEFIRST. After setting expected week start day, all we have run above queries.

SQL

SELECT @@DATEFIRST; 
SET DATEFIRST 7;     /*setting week start to 'Sunday'*/

@@DATEFIRST is local to the session. We can verify it by opening different tabs in SQL Server Management Studio and executing set/select code in the different tabs. For options, please do check SQL Server: Find Week Start And End DateTime.

Month

Month Range

Finding start and end date time of a month.

SQL

DECLARE @dateTimeNow DATETIME ='2019-07-01';    /*yyyy-MM-dd*/
--DECLARE @dateTimeNow DATETIME = GETDATE();    /*now*/

SELECT 
    [StartDate] = DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow), 0),
    [EndDate] = DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow) + 1, -1);

SELECT 
    [StartDateTime] = DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow), 0),
    [EndDateTime] = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @dateTimeNow) + 1, 0));

Populate Months With Range

Generating a month list with a monthly range:

SQL

DECLARE @startDateTime DATETIME ='2019-01-18 03:20:00', _
        @endDateTime DATETIME ='2019-12-18 04:20:00';

WITH Months([Date])
AS 
(
    SELECT [Date] = @startDateTime 
         UNION ALL
         SELECT DATEADD(MONTH, 1, [Date]) 
             FROM   Months 
             WHERE  DATEADD(MONTH, 1, [Date]) <= @endDateTime
), 
MontRange([Date], [StartDateTime], [EndDateTime])
AS
(
    SELECT 
        [Date],
        DATEADD(mm, DATEDIFF(m, 0, [Date]), 0),
        DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, [Date]) + 1, 0))
        FROM Months 
)
SELECT * 
    FROM   MontRange 
    OPTION (MAXRECURSION 0)

Year

Year Range

Finding start and end date time of a year.

SQL

DECLARE @dateTimeNow DATETIME ='2019-07-01'    /*yyyy-MM-dd*/
--DECLARE @dateTimeNow DATETIME = GETDATE();   /*now*/

SELECT
   [StartDate] = DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow), 0),
   [EndDate] = DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow) + 1, -1)
SELECT 
    [StartDateTime] = DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow), 0),
    [EndDateTime] = DATEADD(s, -1, DATEADD(yy, DATEDIFF(yy, 0, @dateTimeNow) + 1, 0))

Populate Years With Range

Generating a year list with a yearly range:

SQL

DECLARE @startDateTime DATETIME ='2017-12-17 03:20:00', _
        @endDateTime DATETIME ='2019-12-19 04:20:00';

WITH Years([Date])
AS 
(
    SELECT [Date] = @startDateTime 
         UNION ALL
         SELECT DATEADD(YEAR, 1, [Date]) 
             FROM   Years 
             WHERE  DATEADD(YEAR, 1, [Date]) <= @endDateTime
), 
YearRange([Date], [StartDateTime], [EndDateTime])
AS
(
    SELECT 
        [Date],
        DATEADD(yy, DATEDIFF(yy, 0, [Date]), 0),
        DATEADD(s, -1, DATEADD(yy, DATEDIFF(yy, 0, [Date]) + 1, 0))
        FROM Years 
)
SELECT * 
    FROM   YearRange 
    OPTION (MAXRECURSION 0)

DateTime Function

After repeating the same code multiple times, I was wondering why not create a date time helper function to find the start/end date time. So here we have it.

Create Function

SQL

IF OBJECT_ID(N'DateTimePart', N'FN') IS NOT NULL
    DROP FUNCTION DateTimePart;
GO
CREATE FUNCTION DateTimePart(@dateTime DATETIME, @startOrEnd VARCHAR(10))
RETURNS DATETIME
AS 
BEGIN
    /*validations*/
    IF @dateTime IS NULL
    BEGIN
        RETURN @dateTime;
    END
    IF @startOrEnd NOT IN('Start', 'End')
    BEGIN
        RETURN CAST('@startOrEnd should be IN(Start, End)' AS INT);
    END

    /*result*/
    DECLARE @result DATETIME;
    SELECT @result =
        CASE @startOrEnd
            WHEN 'Start' THEN DATEADD(DAY, DATEDIFF(DAY, 0, @dateTime), 0)
            WHEN 'End'    THEN DATEADD(SECOND, -1, _
                          DATEADD(DAY, DATEDIFF(DAY, 0, @dateTime) + 1, 0))
        END;

    RETURN @result;
END

Using Function

SQL

DECLARE @dateTime DATETIME ='2019-12-01 17:20:00' /*yyyy-MM-dd HH:mm:ss*/
SELECT 
    [DateTime] = @dateTime,
    [StartDateTime] = dbo.DateTimePart(@dateTime, 'start'),
    [EndDateTime] = dbo.DateTimePart(@dateTime, 'end');

Others

DateTime Now

SQL

SELECT
    [Local] = GETDATE(),
    [Utc] = GETUTCDATE();

DateTime To Date

SQL

SELECT 
    [Date] = CONVERT(DATE, GETDATE()),    --CONVERT(DATE, GETDATE(), 101)
    [Date] = CAST(GETDATE() AS DATE),
    [DateTime] = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0);    --DAY

Day, Month, Year Detail

Name

SQL

SELECT 
    [Day] = DATENAME(WEEKDAY, GETDATE()),
    [Month] = DATENAME(MONTH, GETDATE()),
    [Year] = DATENAME(YEAR, GETDATE());

Number

SQL

SELECT 
    [Day] = DATEPART(WEEKDAY, GETDATE()),
    [Month] = DATEPART(MONTH, GETDATE()),
    [Year] = DATEPART(YEAR, GETDATE());

Add To Date

Adding a Day

SQL

SELECT
    [Today] = GETDATE(),
    [TodayPlusOneDayUsingFunction] = DATEADD(dd, 1, GETDATE()),    /*addsing one day*/
    [TodayPlusOneDayUsingOperator] = GETDATE() + 1;                /*addsing one day*/

Deducing a Second

SQL

SELECT 
    [NowDateTime] = GETDATE(),
    [StatDateTime] =  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0),                            /*removing time details*/
    [EndDateTime] = DATEADD(SECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0));    /*removing time details, adding one date(+1), deducting one second(-1)*/

Deduction Millisecond

To add/deduct MILLISECOND/MICROSECOND/NANOSECOND, it is better to cast source/result to DATETIME2 object rather than DATETIME.

SQL

SELECT 
    [NowDateTime] = GETDATE(),
    DATEADD(MILLISECOND, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)),                /*not as expected, went to next date*/
    /*
        MICROSECOND, MICROSECOND, NANOSECOND need DATETIME2, better to use DATETIME2 data type
        Datetime2 Vs Datetime: https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server
    */
    DATEADD(MILLISECOND, -1, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS DATETIME2)),
    DATEADD(MICROSECOND, -1, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS DATETIME2)),
    DATEADD(NANOSECOND, -1, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AS DATETIME2));

Group By Date

SQL

DECLARE @tblTest TABLE(AddDateTime DATETIME NOT NULL);
INSERT 
    INTO @tblTest 
    VALUES 
    /*yyyy-MM-dd hh:mm:ss*/
    ('2019-04-17 03:20:00'),
    ('2019-04-17 04:20:00'),
    ('2019-04-16 03:20:00'),
    ('2019-04-16 04:20:00')
SELECT 
    [Date] = DATEADD(dd, DATEDIFF(dd, 0, AddDateTime), 0), 
    [Total] = COUNT(AddDateTime)
    FROM @tblTest
    GROUP BY DATEADD(dd, DATEDIFF(dd, 0, AddDateTime), 0);

Limitations

The code may throw unexpected errors for untested inputs. If any, just let me know.

History

11^th July, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)