How to get date between 2 days name from a table

Question

I have 2 day names as input (For Example: "Friday" and "Monday". I need to get need to get all dates from a table in between those Friday and Monday (Friday, Saturday, Sunday, Monday) . If it is "Tuesday" and "Thursday" i need to get (Tuesday, Wednesday and Thursday )

My table is

If days are friday and monday. My Output should be

I tried this

      SELECT
           EMPLID, DUR, DayName, TRC
      FROM DayTable
      WHERE         
          DayName BETWEEN 'FRIDAY' AND  'MONDAY'        
      ORDER BY DUR ASC

but not working, Help me to solve this. Thanks in Advance

where DUR between "date of friday" and "date of monday". You are simply doing a string comparison not date comparison — Pரதீப்
@Pரதீப் I won't' get the date. I will have only the day name as input — Arun D
You can't do it like this. Instead use IN('Friday', 'Saturday', 'Sunday', 'Monday') — Md. Suman Kabir
Please make case for in condition than search with help of in condition — Shan
The solutions giving DATEPART(weekday, dur) are preferable to those giving IN('Friday', 'Saturday', 'Sunday', 'Monday' as these will be language independent (though you have to be careful of the datefirst value as pointed out below). However, those which generate the list of days to compare to give the answer in the form you've specified / may be useful if you only had the day names and not the related dates. — JohnLBevan

sanatsathyan sanatsathyan · Accepted Answer · 2018-01-22T10:47:30

Try this query:

DECLARE @From int,@To int

Create Table #Days(Id int, DayOfWeek Varchar(100))
Insert into #Days Values
(1,'Sunday'),
(2,'Monday'),
(3,'Tuesday'),
(4,'Wednesday'),
(5,'Thursday'),
(6,'Friday'),
(7,'Saturday')

Select @From = Id from #Days where DayOfWeek = 'Friday'
Select @To = Id from #Days where DayOfWeek = 'Monday'

Select T.EMPLID, T.DUR, T.DayName, T.TRC from DayTable T
Inner Join #Days D on T.DayName = D.DayOfWeek AND (D.Id <= @To Or D.Id >= @From)

Hope this helps!

Update

Here's the same solution in a table valued function:

create function dbo.DaysBetween (
    @DayFrom nvarchar(16)
    , @DayTo nvarchar(16) 
) returns @results table ([DayName] nvarchar(16))
as
begin
    declare @daynames table (id smallint not null, [dayname] nvarchar(16) not null)
    insert @daynames(id, [dayname])
    values (0, 'Monday'),(1, 'Tuesday'),(2, 'Wednesday'),(3, 'Thursday'),(4, 'Friday'),(5, 'Saturday'),(6, 'Sunday')

    declare @dayFromInt smallint
    , @dayToInt smallint 

    select @dayFromInt = id from @daynames where [dayname] = @DayFrom
    if (@dayFromInt is null) 
    begin
        --hacky trick from https://stackoverflow.com/a/4681815/361842
         set @dayFromInt = cast(('Invalid Day From Name: ' + @DayFrom) as int)
         return
    end
    select @dayToInt = id from @daynames where [dayname] = @DayTo
    if (@dayToInt is null) 
    begin
        --hacky trick from https://stackoverflow.com/a/4681815/361842
         set @dayToInt = cast(('Invalid Day To Name: '+ @DayTo) as int)
         return
    end


    insert @results ([dayname])
    select [dayname]
    from @daynames
    where 
    (
        (@dayFromInt <= @dayToInt) and (id between @dayFromInt and @dayToInt)
        or
        (@dayFromInt > @dayToInt) and (id >= @dayFromInt or id <= @dayToInt)
    )

    return 
end
go

Here are some example scenarios:

select * from dbo.DaysBetween('Monday','Friday')
select * from dbo.DaysBetween('Friday','Monday')
select * from dbo.DaysBetween('Tuesday','Thursday')
select * from dbo.DaysBetween('Thursday','Tuesday')
select * from dbo.DaysBetween('Christmasday','Monday')
go --required to get this result after the above error
select * from dbo.DaysBetween('Monday','Holiday')

To use this in your query, you'd do:

SELECT EMPLID
, DUR
, DayName
, TRC
FROM DayTable
WHERE         
[DayName] in 
(
    select [DayName] 
    from dbo.DaysBetween('Friday','Monday')
)        
ORDER BY DUR ASC

How to get date between 2 days name from a table

4 Answers

Update