Click here to Skip to main content
14,236,142 members

SQL Server: DateTime Range Pro

Rate this:
4.29 (5 votes)
Please Sign up or sign in to vote.
4.29 (5 votes)
11 Jul 2019CPOL
Populating DateTime range in SQL Server

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.

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.

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:

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.

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:

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.

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.

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:

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.

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:

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

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

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

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

DateTime To Date

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
SELECT 
    [Day] = DATENAME(WEEKDAY, GETDATE()),
    [Month] = DATENAME(MONTH, GETDATE()),
    [Year] = DATENAME(YEAR, GETDATE()); 
Number
SELECT 
    [Day] = DATEPART(WEEKDAY, GETDATE()),
    [Month] = DATEPART(MONTH, GETDATE()),
    [Year] = DATEPART(YEAR, GETDATE()); 

Add To Date

Adding a Day
SELECT
    [Today] = GETDATE(),
    [TodayPlusOneDayUsingFunction] = DATEADD(dd, 1, GETDATE()),    /*addsing one day*/
    [TodayPlusOneDayUsingOperator] = GETDATE() + 1;                /*addsing one day*/
Deducing a Second
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.

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

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

  • 11th July, 2019: Initial version

License

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

Share

About the Author

DiponRoy
Bangladesh Bangladesh
No Biography provided

Comments and Discussions

 
QuestionTwo thoughts... Pin
NeverJustHere12hrs 24mins ago
memberNeverJustHere12hrs 24mins ago 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 11 Jul 2019

Stats

2.2K views
77 downloads
8 bookmarked