如何在Server中的两个WeekDay名称字段之间获取WeekDay名称

Question

我有两篇带有工作日名称的专栏，我的问题是如何在这两个工作日名称之间获得所有工作日名称。

例如：

 WeekStartDate      WeekEndDate  
   monday             friday

我想要这样的结果

Monday, Tuesday, Wednesday, Thursday, Friday

请帮帮我

谢谢

Answer 1

您能不能在Where子句中使用中间操作符，并在中间添加一个INT列，这样就不能在nvarchar上工作。

            Create table #temp(
            data int,
            dayN Nvarchar(100)
            ,dayc int
            )
            SELECT * FROM #TEMP
            Insert into #temp values (10,'Monday',2)
            Insert into #temp values (20,'tuesday',3)
            Insert into #temp values (30,'wednesday',4)
            Insert into #temp values (40,'thursday',5)
            Insert into #temp values (50,'friday',6)

            SELECT Data
            FROM #TEMP
            where dayN between 'Monday' and 'Wednesday'
            --Above query gives wrong result

            SELECT Data
            FROM #TEMP
            where dayC between 3 and 6

Answer 2

您可以这样做：

declare @tbl table (WeekStartDate  nvarchar(60),WeekEndDate    nvarchar(60) )
insert into @tbl values ('Monday', 'Friday')

declare @weeknums table (name nvarchar(60), num int)
insert into @weeknums values 
 ('Monday'    , 1 ),
 ('Tuesday'   , 2 ),
 ('Wednesday' , 3 ),
 ('Thursday'  , 4 ),
 ('Friday'    , 5 ),
 ('Saturday'  , 6 ),
 ('Sunday'    , 7 )

declare @min nvarchar(max) = (select min(num) 
                              from @tbl t
                              join @weeknums w on t.WeekStartDate = w.name or t.WeekEndDate = w.name)

declare @max nvarchar(max) = (select max(num) 
                              from @tbl t
                              join @weeknums w on t.WeekStartDate = w.name or t.WeekEndDate = w.name)

select w.* 
from @weeknums w
where w.num between @min and @max