blocks|key|4273407|text|好了，这一定行得通。它会显示每一行有时间冲突的行；如果你取消最后一行的注释，它也会显示没有冲突的行|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4273408|declare+@test_DateTimeStart+datetime=dateadd(day,4,'20:00:00')+--+Check+from+Monday+20:00
declare+@test_DateTimeEnd+datetime=dateadd(day,5,'20:00:00')+--+Check+to+Tuesday+20:00

select+q2.result,c.*
from+
++++#Calendar+c
++++cross+apply+
++++(+++select+
++++++++dateadd(day,DayWeek,convert(datetime,HourStart))+as+DateTimeStart,
++++++++dateadd(day,DayWeek%2B1,convert(datetime,HourEnd))+as+DateTimeEnd
++++)q
++++cross+apply+(select
++++++++case+when+exists
++++++++(
++++++++++++select+1+from+#Calendar+c2+where+
++++++++++++++++(@test_DateTimeStart>DateTimeStart+and+@test_DateTimeStart<DateTimeEnd)
++++++++++++++++or
++++++++++++++++(@test_DateTimeEnd++>DateTimeStart+and+@test_DateTimeEnd++<DateTimeEnd)
++++++++)
++++++++then+'Time+is+taken+due+to+this+row+-->'
++++++++else+'Time+is+free+due+to+this+row+-->'
++++++++end+as+result
++++)q2
--where+result='Time+is+taken+due+to+this+row+-->'|code-block|syntax|javascript|4273409|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

OK, this must work. It shows every line which has a time conflict; if you uncomment the last where it will show lines without conflict too

<pre><code>declare @test_DateTimeStart datetime=dateadd(day,4,'20:00:00') -- Check from Monday 20:00
declare @test_DateTimeEnd datetime=dateadd(day,5,'20:00:00') -- Check to Tuesday 20:00

select q2.result,c.*
from 
 #Calendar c
 cross apply 
 ( select 
 dateadd(day,DayWeek,convert(datetime,HourStart)) as DateTimeStart,
 dateadd(day,DayWeek+1,convert(datetime,HourEnd)) as DateTimeEnd
 )q
 cross apply (select
 case when exists
 (
 select 1 from #Calendar c2 where 
 (@test_DateTimeStart&gt;DateTimeStart and @test_DateTimeStart&lt;DateTimeEnd)
 or
 (@test_DateTimeEnd &gt;DateTimeStart and @test_DateTimeEnd &lt;DateTimeEnd)
 )
 then 'Time is taken due to this row --&gt;'
 else 'Time is free due to this row --&gt;'
 end as result
 )q2
--where result='Time is taken due to this row --&gt;'
</code></pre>

blocks|key|4273865|text|使用下面的代码来获取当前星期几的实时时间。然后与您保存的数据进行比较|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4273866|var+myHttpWebRequest+=+(HttpWebRequest)WebRequest.Create("http://www.google.com");

var+response+=+myHttpWebRequest.GetResponse();

string+todaysDates+=+response.Headers["date"];

return++DateTime.ParseExact(todaysDates,"ddd,+dd+MMM+yyyy+HH:mm:ss'GMT'",CultureInfo.InvariantCulture.DateTimeFormat,DateTimeStyles.AssumeUniversal).DayOfWeek;|code-block|syntax|javascript|4273867|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use below code to get the current day of week real time. then compare with your save data

<pre><code>var myHttpWebRequest = (HttpWebRequest)WebRequest.Create("http://www.google.com");

var response = myHttpWebRequest.GetResponse();

string todaysDates = response.Headers["date"];

return DateTime.ParseExact(todaysDates,"ddd, dd MMM yyyy HH:mm:ss'GMT'",CultureInfo.InvariantCulture.DateTimeFormat,DateTimeStyles.AssumeUniversal).DayOfWeek;
</code></pre>

blocks|key|4273447|text|这将会解决这个问题。它将不能使用索引，因为它在比较时对列进行计算：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4273448|DECLARE+@from+time+=+'22:59',+@to+time+=+'23:01'

DECLARE+@DayWeek+int+=+2

;WITH+CTE+as
(
SELECT+
++dateadd(d,+@DayWeek,+cast(@from+as+datetime))+startdate,
++dateadd(d,+
++++CASE+WHEN+@to+<+@from+
+++++++++THEN+@DayWeek+%2B+1
+++++++++ELSE+@DayWeek
++++END,
++cast(@to+as+datetime))+enddate
)
SELECT+CASE+WHEN+
not+exists
(
++SELECT+*+
++FROM+#Calendar+c+
++WHERE+cte.startdate+<+dateadd(d,+CASE+WHEN+HourEnd+<+HourStart+
++THEN+DayWeek+%2B+1+ELSE+DayWeek+END,+cast(HourEnd+as+datetime))
++AND+cte.enddate+>+dateadd(d,+DayWeek,+cast(HourEnd+as+datetime))
)+THEN+'Available'+ELSE+'Not+available'+END
FROM+CTE|code-block|syntax|javascript|4273449|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will solve it. It will not be able to use index because it uses calculation on columns when comparing:

<pre><code>DECLARE @from time = '22:59', @to time = '23:01'

DECLARE @DayWeek int = 2

;WITH CTE as
(
SELECT 
 dateadd(d, @DayWeek, cast(@from as datetime)) startdate,
 dateadd(d, 
 CASE WHEN @to &lt; @from 
 THEN @DayWeek + 1
 ELSE @DayWeek
 END,
 cast(@to as datetime)) enddate
)
SELECT CASE WHEN 
not exists
(
 SELECT * 
 FROM #Calendar c 
 WHERE cte.startdate &lt; dateadd(d, CASE WHEN HourEnd &lt; HourStart 
 THEN DayWeek + 1 ELSE DayWeek END, cast(HourEnd as datetime))
 AND cte.enddate &gt; dateadd(d, DayWeek, cast(HourEnd as datetime))
) THEN 'Available' ELSE 'Not available' END
FROM CTE
</code></pre>

I have a table, which has columns to store the start time and end of the service (I do not have date and time, only the day of the week). I have to check if a time is available for the week, considering that a service starts on Monday and ends on Tuesday. Can someone help me?

<pre><code>CREATE TABLE #Calendar (
 Name VARCHAR(100),
 DayWeek INT,
 HourStart TIME,
 HourEnd TIME
);

INSERT INTO #Calendar (Name, DayWeek, HourStart, HourEnd)
 VALUES ('Make a Cake', 1, '19:00:00', '07:00:00');

INSERT INTO #Calendar (Name, DayWeek, HourStart, HourEnd)
 VALUES ('Make a cookie', 1, '07:00:00', '19:00:00');

INSERT INTO #Calendar (Name, DayWeek, HourStart, HourEnd)
 VALUES ('Make a cookie', 2, '19:00:00', '23:00:00');
</code></pre>

Check hours from one day to another in tsql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个表，其中包含用于存储服务的开始时间和结束时间的列(我没有日期和时间，只有星期几)。考虑到服务在周一开始，周二结束，我必须检查一周是否有时间可用。有人能帮我吗？CREATE TABLE #Calendar (    Name VARCHAR(100),    DayWeek INT,    HourStart T...

问在tsql中检查从一天到另一天的时间
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在tsql中检查从一天到另一天的时间EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在tsql中检查从一天到另一天的时间
EN