SQLTeam.com | Weblogs | Forums

Week's date range


#1

What is the best way to dynamically get the week date range, starting on Sunday and ending on Saturday?

So for example, today's date is 2018_08_29

I would like:

2018_08_26 Sunday
2018_08_27 Monday
2018_08_28 Tuesday
2018_08_29 Wednesday
2018_08_30 Thursday
2018_08_31 Friday
2018_09_01 Saturday

My first attempt would be using a cursor loop, loading in the date into a temp table, with this as a starting point:
SELECT DATEADD(DD, 1 - DATEPART(DW, GETDATE()), GETDATE())

However, I just want to know if there is even a better way of doing this, without a cursor loop?

Thanks


#2

Heavens no, don't use a while loop or cursor loop. Use a simple data calc and a "standard" tally table:

;WITH
cteTally10Base AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally10 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally10Base
)
SELECT DATEADD(DAY, t.number, sunday_date) AS current_week_dates
FROM cteTally10 t
CROSS APPLY (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, GETDATE()) % 7, CAST(GETDATE() AS date)) AS sunday_date
) AS get_prior_sunday_date
WHERE t.number BETWEEN 0 AND 6
ORDER BY t.number

#3
select cast(dateadd(day,-(datediff(day,-1,current_timestamp)%7)+n,current_timestamp) as date) as thedate
      ,datename(weekday,dateadd(day,-(datediff(day,-1,current_timestamp)%7)+n,current_timestamp)) as thedatename
  from (values(0),(1),(2),(3),(4),(5),(6)) as tally(n)
;

#4

Are you wanting to return the list of day in this week or the begin and end date of this week? If you want the list of days Scott's or Bitsmeds's solutions are awesome. If you want the begin and end date:

SET DATEFIRST 7;

WITH Wk
     AS (SELECT
            DateAdd(WEEK, DateDiff(WEEK, 0, GetDate()), 0) - 1 StartWeek
          , DateAdd(WEEK, 1, DateAdd(WEEK, DateDiff(WEEK, 0, GetDate()), 0)) - 2 EndWeek)
     SELECT
        Wk.StartWeek
      , DateName(dw, Wk.StartWeek) StartDay
      , Wk.EndWeek
      , DateName(dw, Wk.EndWeek) EndDay
     FROM
        Wk;

image


#5

WOW! WOW! WOW!

I have never seen any of these methods of getting a range like this. Pretty heavy duty!

Scott, I do have a question for you. Since you avoid using cursor loops, and if this question was to get the range of dates in a month which varies between 28 - 31 days, how would you do this? I noticed if we use the tally method from yours and BitsMed, a fixed number of data have to be entered in the value section. For example: (0),(1),(2),(3),(4),(5),(6)

Therefore, for days (between 28 - 31 days) in a month, would you then use a loop?


#6

No, I wouldn't loop for that either.

And that's one of the reasons I used the tally structure I did: it easily adjusts to add more tally rows. For example, this will give you up to 100 rows. Inevitably you will at some point need more tally rows.

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
    FROM cteTally10 t1
    CROSS JOIN cteTally10 t2
)
SELECT * 
FROM cteTally100

#7

And if you need more than 100:

With E1(n) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) As E0(X)),
E10(n) As (Select 1 From E1 a, E1 b, E1 c,E1 d, E1 f, E1 g, E1 h, E1 i, E1 j)
Select Top (@Count) Row_Number() Over (Order By n) As Value from E10

Will give you @Count numbers up to 10^9, which is more than enough for most purposes.


#8

Scott, maybe I did a poor job of explaining about the month range or maybe I did not understand your reply.

In the first post, I asked about week which we know it will always be 7 days. In the followup question to you, I asked about month which could vary anywhere 28 to 31 days. April is 30 days and May is 31 days.

So i guess my question is would I have to hard code 28-31 days in the VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0) section? How would I handle dynamic days depending on the month? Thanks


#9

The tally table merely generates numbers using cross joins and the Row_number function. By adding more tally rows, I believe Scott means another cross join.

Every "row" in the tally table in the example is a Cartesian join exponentially increasing the number number of rows numbers the tally produces. You could also use a select from values expression to return 31 numbers and use the DateAdd function to calculate the dates.

-- =============================================
-- Author:		Joe T
-- Create date: 12/11/2017
-- Description: 	
-- select num from dbo.itfTally(255)
-- =============================================
ALTER FUNCTION [dbo].[itfTally] 
(	
	@Limit int
)
RETURNS table WITH SCHEMABINDING
AS
RETURN 
(
	WITH n10 (num)        AS ( SELECT num FROM (VALUES (0),(1),(3),(4),(5),(6),(7),(8),(9)) n(num) ) 
      , n100 (num)       AS ( SELECT n10.num FROM n10 n10 CROSS JOIN n10 n)
      , n10000 (num)     AS ( SELECT n100.num FROM n100 n100 CROSS JOIN n100 n)
      , n100000000 (num) AS ( SELECT n10000.num FROM n10000 n10000 CROSS JOIN n10000 n)
      , rn (num)         AS ( SELECT 0 UNION SELECT Row_Number() OVER (ORDER BY n.num)  FROM n100000000 n) 
   SELECT
      r.num
   FROM rn r
   WHERE r.num<=@Limit
)

You can easily create a tally function like the one above and generate as many numbers as you need.


#10

This function is used in reporting in month over month comparisons.


/* RCIT Database Support
   By:   Joe Torre
   On:   Aug 8th 2018
   For:  This funtion returns monthly inerval values for the range passed in
   DECLARE @d date = GetDate();
   SELECT
      BegDt
   ,  EndDt
   FROM dbo.MonthlyCalendar ('20150101', @d);
*/
ALTER FUNCTION [dbo].[MonthlyCalendar] (@BegDate datetime, @EndDate datetime) RETURNS TABLE
AS RETURN
(

	WITH n1 AS (SELECT n FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))t(n))--10
      , n2 AS (SELECT n.n FROM n1 AS n CROSS JOIN n1)--100
      , n3 AS (SELECT n.n FROM n2 AS n CROSS JOIN n2)--10000
      , nums AS (SELECT 0 num UNION ALL SELECT Row_Number() OVER (ORDER BY(SELECT NULL)) num FROM n3)
      , Calendar (BegDt, EndDt) AS (
                  SELECT 
                        DateAdd(m, nums.num, @BegDate) 
                      , DateAdd(MILLISECOND, -3, DateAdd(m, 1, DateAdd(m, nums.num,@BegDate)))
             
                  FROM nums
                  )
   SELECT
        c.BegDt
      , c.EndDt
   FROM 
      Calendar c
   WHERE
      c.BegDt < @EndDate
)

#11

Or, using my Tally table function (which starts at 1, as I find that more useful)

Create Or Alter Function [dbo].[Tally](@Count Int) 
Returns Table
With SchemaBinding
As Return
(
With E1(n) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) As E0(X)),
E10(n) As (Select 1 From E1 a, E1 b, E1 c,E1 d, E1 f, E1 g, E1 h, E1 i, E1 j)
Select Top (@Count) Row_Number() Over (Order By n) Value From E10
)
GO

It becomes a simple case of:

Declare @Today Date = GetDate()

Select 
	DateAdd(Day, T.value, EOMonth(@Today, -1))
From Tally(DatePart(Day, EOMONTH(@Today))) T

#12

Very cool Joe and Andy! Thank you so much!

Wow! in the past few days, weeks, months, so much new knowledge. I have never seen tally before. When was it introduced to Microsoft SQL Server?


#13

It's not really "introduced to SQL Server" as such. It's a programming technique that has been applicable in every SQL dialect ever. There have been many different ways of implementing it, the most basic being to just create a real table and populate it with a big range of numbers using a loop, but the general theory that there are a whole class of problems that can be quickly solved with a table full of continuous ascending numbers has been a staple of set-based programming for a long time.

If anything, the minor annoyance is that it hasn't been added to SQL Server as an intrinsic function that could probably be even more efficient and avoid cluttering up query plans with boilerplate number generation (you can do that by using a real table, but it has higher IO overheads than just generating numbers on the fly)


#14

Hi Andy and Joe,

Thanks for your replies.

I finally got a chance to come back to this and looked into yourfunctions. They work!

I am able to understand most of it but there are some lines I don't understand. Can you help me understand the logic on these lines?

First, what is "With SchemaBinding"?

Second, what are these lines doing? I guess trying to understand the tallying part:

WITH
	  E1(n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) As E0(X))
	, E10(n) AS (SELECT 1 FROM E1 a, E1 b, E1 c,E1 d, E1 f, E1 g, E1 h, E1 i, E1 j)

	--PURPOSE: Return the passed in value from the table E10
	SELECT TOP (@Count) Row_Number() OVER (ORDER BY n) MyValue FROM E10

#15

SchemaBinding effectively "locks" the structure of all referenced objects (tables, views etc) so they cannot be changed. In doing so, the optimizer can apply a more rigorous series of optimizations, such as deciding a function is deterministic (always returns the same result from the same input), which can produce better performing code. For functions like this which don't reference any objects at all, there is literally no down side to marking the function as schema bound so it's always a good idea.

As to the tallying, the first common table expression, E1, creates a table with 10 rows (each just containing the number 1). The second common table expression, E10, then CROSS JOINs that table to itself several times (using the "," syntax for CROSS JOINS purely for brevity). That has the effect of a table with 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 * 10 rows in it. Finally we select @Count rows from that table, returning a row number each time - thus effectively generating a sequence of @Count numbers.


#16

Andy,

Should there also be a "E1 k" to make it 10, matching the E1 table? I only see 9 in the E10 table.

Also, E1(n) and E10(n), what exactly is the "n" doing as well as E0(X) for the "X"?

Thanks


#17

Er, yeah, probably. Not that it matters much, it will already generate a crazy number of rows and you can keep adding on another cross join if you need more.

As to the "n", it's just a column name alias because all columns in a CTE need to be named and that syntax is just a bit shorter. The following two queries are equivalent:

With X As (Select 1 As A, 2 As B, 3 As C, 4 As D) Select * From X;
With X(A,B,C,D) As (Select 1,2,3,4) Select * From X;

#18

You are really cool Andy! Thank you!


#19

Scott, Joe or Bitsmed,

You used something like this in your query: datediff(day,-1,current_timestamp)%7

Usually when I use DATEDIFF, I provide two dates (Start and End). In this case, you are providing numbers. Can you help me understand and the usage of mod (%) of 7? I am going through and breaking down your queries to understand. Yes, you knowledge transfer is not wasted. I go through every single query and play with them. Thank You

Why does something like this returns a value of 43285.

SELECT DATEDIFF(DAY, -1, '2018-07-05') My_Date

#20

The number gets translated to a date.

Date 0 = '1900-01-01'. Date -1 (I never use that "trick", because I don't like negative dates) = '1899-12-31' (i.e. one day before day 0).

The %7 strips out whole weeks. This is good for determining the day of week of a given date. Date 0 is a Monday. So, if the datediff % 7 = 0, then that date is also a Monday. If it's 3, then that date is a Thursday (three days past Monday can only be Thursday).

If you need to determine if a day is Sunday, for example, you could use day 6 and % 7 and check for 0.

One big advantage of this method is that the @@DATEFIRST and/or language settings don't affect the result, you always get the correct result regardless of those settings. I'd avoid code that adjusts using @@DATEFIRST in the calc. It's just prone to confusion/error.