如何从表中获取2天之间的日期名称

Question

我有 2 天名称作为输入（例如：“星期五”和“星期一”。我需要从周五和周一（周五、周六、周日、周一）之间的表格中获取所有日期。如果是“星期二”和“星期四”，我需要得到（星期二，星期三和星期四）

我的桌子是

如果日子是星期五和星期一。我的输出应该是

我试过这个

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

但不工作，帮我解决这个问题。提前致谢

Answer 1

如果在DUR上使用DATEPART函数，将获得工作日的整数表示。

因此，更新WHERE子句：

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

其中datepart将给出工作日整数:SUN -

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

Answer 2

您不能这样使用之间运算符。Datename返回一个字符串，对于所有在词汇上位于“星期五”和“星期一”之间的日名，之间运算符是tru。

我建议使用

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

或使用

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

Answer 3

尝试以下查询:

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)

希望这有帮助！

以下是表值函数中的相同解决方案:

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

以下是一些示例场景：

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')

要在查询中使用它，您需要:

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