We help IT Professionals succeed at work.

Query Sql server

Good morning

how can I make a query Sql where I pass a range of dates and returns me a column with the day and another column with the day in string.


date.                        day
04/11/2017         Saturday
05/11/2017           Sunday


Thank you
Comment
Watch Question

Here is a simplest way, not necessarily the best to create a huge range. You can easily encapsulate the entire logic within a table returning function.

declare @dates TABLE(dt date)    
declare @dateFrom datetime
declare @dateTo datetime

set @dateFrom = '2001/01/01'
set @dateTo = '2001/01/12'

while(@dateFrom < @dateTo)
begin
   select @dateFrom = dateadd(day, 1,@dateFrom)
   insert into @dates 
   select @dateFrom
end

select dt, datename(DW, dt)
from @dates

Open in new window

And here is a function:

create function [dbo].[GetDateTable]
(
   @dateFrom date
  ,@dateTo date
)
returns @dates table (dt date)    
as
begin

  while(@dateFrom < @dateTo + 1)
  begin
     insert into @dates 
     select @dateFrom
     select @dateFrom = dateadd(day, 1,@dateFrom)
  end

  return
end
go

select dt, datename(dw, dt) from [dbo].[GetDateTable]('20171101', '20171130')

Open in new window

Database Expert
Awarded 2016
Top Expert 2016
Commented:
Loops are slow. We should always use Numbers for this kind of Range things.

Please try full and tested solution -

DECLARE @StartRange AS DATETIME = '2017/12/01'
DECLARE @EndRange AS DATETIME = '2017/12/31'

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
SELECT CAST(DATEADD(d,Number-1,@StartRange) AS DATE) Datet ,DATENAME(DW, DATEADD(d,Number-1,@StartRange)) DayName FROM Series 
WHERE  DATEADD(d,Number-1,@StartRange) <= @EndRange
ORDER BY Number

Open in new window


Output

/*------------------------
OUTPUT
------------------------*/
Datet      DayName
---------- ------------------------------
2017-12-01 Friday
2017-12-02 Saturday
2017-12-03 Sunday
2017-12-04 Monday
2017-12-05 Tuesday
2017-12-06 Wednesday
2017-12-07 Thursday
2017-12-08 Friday
2017-12-09 Saturday
2017-12-10 Sunday
2017-12-11 Monday
2017-12-12 Tuesday
2017-12-13 Wednesday
2017-12-14 Thursday
2017-12-15 Friday
2017-12-16 Saturday
2017-12-17 Sunday
2017-12-18 Monday
2017-12-19 Tuesday
2017-12-20 Wednesday
2017-12-21 Thursday
2017-12-22 Friday
2017-12-23 Saturday
2017-12-24 Sunday
2017-12-25 Monday
2017-12-26 Tuesday
2017-12-27 Wednesday
2017-12-28 Thursday
2017-12-29 Friday
2017-12-30 Saturday
2017-12-31 Sunday

(31 row(s) affected)

Open in new window

Author

Commented:
Thank you