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ரதீப், Jan 22 '18 at 10:30
@Pரதீப் I won't' get the date. I will have only the day name as input — Arun D, Jan 22 '18 at 10:35
You can't do it like this. Instead use `IN('Friday', 'Saturday', 'Sunday', 'Monday')` — Md. Suman Kabir, Jan 22 '18 at 10:39
Please make case for in condition than search with help of in condition — Shan, Jan 22 '18 at 10:41
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, Jan 22 '18 at 13:08

score 1 · Answer 1 · answered Jan 22 '18 at 10:40

You cannot use the between operator like that. Datename returns a string, and the between operator is tru for all daynames that lexigraphically falls between "Friday" and "Monday".

I would suggest using

 DayName in  ('FRIDAY','SATURDAY','SUNDAY',  'MONDAY'   )

Or use

set datefirst 2
...
where  datepart(weekday,DUR)>3

score 1 · Answer 2 · answered Jan 22 '18 at 10:43

If you use the DATEPART function on DUR, you will get the integer representation for the weekday.

So updating your WHERE clause:

SELECT
       EMPLID, DUR, DayName, TRC
  FROM DayTable
  WHERE         
      DATEPART(weekday, DUR) BETWEEN 2 AND 6         
  ORDER BY DUR ASC

Where datepart will give you the weekday integer: SUN -> 1, MON-> 2 ... SAT -> 7

https://learn.microsoft.com/pt-br/sql/t-sql/functions/datepart-transact-sql

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

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

score 1 · Answer 4 · answered Jan 22 '18 at 10:57

If you know your sql server's setting start day of the week is Sunday, then you can try this query :

  SELECT
       EMPLID, DUR, DayName, TRC
  FROM DayTable
  WHERE         
      DATEPART(WEEKDAY, DUR) <= 2  or DATEPART(WEEKDAY, DUR) >= 6
  ORDER BY DUR ASC

Where week index of Monday = 2 and Friday = 6

How to get date between 2 days name from a table

4 Answers4

Update