0
for example:
--fromdate         todate
--2020/10/1       2020/11/01

Output should be like:

--Days           dayname
-------       ---------------
--2020/10/1  Monday
     ..        ..
Squirrel
  • 23,507
  • 4
  • 34
  • 32
Kartheek
  • 1
  • 4

2 Answers2

0

You can use DATENAME to get the WEEKDAY and filter by it:

DATENAME ( WEEKDAY , my_date )

Something like this (the code for generating days is from here)

declare @dt datetime, @dtEnd datetime
set @dt = '2020/10/1' 
set @dtEnd = '2020/11/01'

select dateadd(day, number, @dt)
       ,DATENAME ( WEEKDAY , dateadd(day, number, @dt) )
from 
    (select number from master.dbo.spt_values
     where [type] = 'P'
    ) n
where dateadd(day, number, @dt) < @dtEnd
    AND DATENAME ( WEEKDAY , dateadd(day, number, @dt) )  NOT IN ('Saturday', 'Sunday')
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • why we used this (select number from master.dbo.spt_values where [type] = 'P' ) n – Kartheek Oct 16 '20 at 05:29
  • 1
    @Kartheek It's just an example of generating dates in range. I guess you have such code. The important stuff is to use `DATENAME` to exclude the weekends. – gotqn Oct 16 '20 at 05:31
0

I think this query works well.

declare @result table
(
    Days varchar(10),
    DayName varchar(20)
)
declare @fromDate date = '2020/10/01'
declare @toDate date = '2020/11/01'

while @fromDate <= @toDate
begin
    if DATENAME(weekday, @fromDate) not in ('Saturday', 'Sunday')
    begin
        insert into @result(Days, DayName)
        values(@fromDate, DATENAME(weekday, @fromDate))
    end
    set @fromDate = DATEADD(day, 1, @fromDate)
end
select *
from @result