英文:
Show date range start date and end date
问题
以下是翻译好的内容：
SET @startDay = '2023-03-30';
SET @LastDay = '2023-04-15';
SELECT
ID,
Start,
End
From tbl
WHERE (Start <= @startDay AND End >= @LastDay)
OR (Start >= @startDay AND End <= @LastDay)
查询将显示记录1、2、3、4、7、8、9，但不包括5、6、10。不幸的是，这个查询不起作用。我该如何修改查询？
英文:
I have a table with a start and end date. I want to output all records that fall within a range. The range would be from 2023-03-30 to 2023-04-15. All records that are within the time window need to be output, including records that start earlier and end later.
ID Start End
1 2023-02-01 2023-06-15
2 2023-03-24 2023-04-12
3 2023-04-10 2023-04-20
4 2023-04-01 2023-04-05
5 2023-03-10 2023-03-28
6 2023-04-16 2023-04-20
7 2023-03-31 2023-04-15
8 2023-03-30 2023-04-15
9 2023-04-15 2023-04-20
10 2023-04-16 2023-04-20
SET @startDay = '2023-03-30';
SET @LastDay = '2023-04-15';
SELECT
ID,
Start,
End
From tbl
WHERE (Start <= @startDay AND End >= @LastDay)
OR (Start >= @startDay AND End <= @LastDay)
A query would show records 1, 2, 3, 4, 7, 8, 9 but not 5, 6, 10.
Doesn't work unfortunately. How would I have to change the query?
答案1
得分: 2
如果您需要获取时间段的任何部分出现在您的范围内的行，请确保结束日期在开始时间之后，并且开始日期在结束日期之前。
SET @StartDay = '2023-03-30';
SET @LastDay = '2023-04-15';
SELECT ID,
Start,
[End] -- End 是一个保留关键字，不应用于对象/列名称
FROM dbo.tbl
WHERE [End] >= @StartDay -- End 是一个保留关键字，不应用于对象/列名称
AND Start <= @LastDay;
英文:
If you need to get rows where any part of the time period appears in your range, then you need to check that the end date is after the start time and the start date is before the end date.
SET @StartDay = '2023-03-30';
SET @LastDay = '2023-04-15';
SELECT ID,
Start,
[End] --End is a reserved keyword, it should not be used for object/column names
FROM dbo.tbl
WHERE [End] >= @StartDay --End is a reserved keyword, it should not be used for object/column names
AND Start <= @LastDay;
答案2
得分: 0
请确保将列名 end 用双引号括起来，以声明它不会用作关键字。
尝试以下代码：
DECLARE @start_date DATE = '2023-03-30';
DECLARE @end_date DATE = '2023-04-15';
SELECT *
FROM tab
WHERE "End" >= @start_date
AND Start <= @end_date;
英文:
Do not forget to wrap your column name end in double quotation marks to declare that it is not used as a keyword.
Try this
DECLARE @start_date DATE = '2023-03-30';
DECLARE @end_date DATE = '2023-04-15';
SELECT *
FROM tab
WHERE "End" >= @start_date
AND Start <= @end_Date;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论