英文:
Use a variable containing a string of dates in an update statement in SQL
问题
Here is the translated code portion:
我想要一个脚本，它会更新一个日期表，如果日期存在于给定列表中，则将bankHoliday标志设置为true。
这个列表是用户每年或两年更新一次的：
```sql
DECLARE @dateList VARCHAR(MAX) = '2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27,
2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01,
2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26,
2025-08-25, 2025-12-25, 2025-12-26';
更新发生在一个较长脚本的底部，因此虽然我可以在顶部告诉用户“确保转到第300行左右并更新节假日列表”，但我更喜欢在顶部将此列表存储在一个变量中，然后在更新中引用它。
我遇到的第一个问题是要使其易于查看，它可以跨多行并包含空格，就像上面的示例一样。因此，我有以下代码来删除所有错误字符：
-- 用单引号替换连字符
SET @dateList = REPLACE(@dateList, ',', ''',''');
-- 用单个空格替换多个空格和换行符
SET @dateList = REPLACE(REPLACE(@dateList, CHAR(13) + CHAR(10), ' '), ' ', '');
-- 在开头和结尾添加单引号
SET @dateList = '''' + @dateList + '''';
此时执行 select @dateList 返回：
'2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'
因此，现在，在脚本底部，我想在我的更新中使用 @dateList：
我尝试了以下代码，但每次都会收到相同的错误：
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
SELECT value FROM STRING_SPLIT(@dateList, ',')
);
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
@dateList
);
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
SELECT CONVERT(datetime, value) FROM STRING_SPLIT(@dateList, ',')
);
再次说明，如果我将 @dateList 的结果直接放入更新中，它可以正常工作：
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
'2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'
);
但如果可能的话，我想避免这样做，因为它需要信任用户转到脚本底部。
<details>
<summary>英文:</summary>
I am wanting to have a script which updates a table of days, setting the bankHoliday flag to true if it exists in a given list.
This list is something a user would update every year or two:
```sql
DECLARE @dateList VARCHAR(MAX) = '2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27,
2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01,
2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26,
2025-08-25, 2025-12-25, 2025-12-26';
The update happens at the bottom of a long script, so though I can tell the user at the top something like "--make sure to go to ~line 300 and update the list of bank holidays", I would prefer to have this list at the top in a variable and then refer to it down in the update.
The first problem I have is for it to be easily visible, it can be on multiple lines and have spaces, like the above example. So I have this bit of code following it to strip out all the wrong characters:
-- Replace the dashes with single quotes
SET @dateList = REPLACE(@dateList, ',', ''',''');
-- Replace multiple spaces and new lines with a single space
SET @dateList = REPLACE(REPLACE(@dateList, CHAR(13) + CHAR(10), ' '), ' ', '');
-- Add single quotes at the beginning and end
SET @dateList = '''' + @dateList + '''';
Doing select @dateList at this point returns this:
'2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'
So now, down at the bottom of the script, i want to use @dateList in my update:
I tried the following, but each time get the same error:
Conversion failed when converting date and/or time from character string.
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
SELECT value FROM STRING_SPLIT(@dateList, ',')
);
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
@dateList
);
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
SELECT CONVERT(datetime, value) FROM STRING_SPLIT(@dateList, ',')
);
Again, if i put the result of @dateList directly into the update, it works fine:
UPDATE days
SET days.PublicHoliday = 1
WHERE days.Date IN
(
'2024-01-01','2024-03-29','2024-04-01','2024-05-06','2024-05-27','2024-08-26','2024-12-25','2024-12-26','2025-01-01','2025-04-18','2025-04-21','2025-05-05','2025-05-26','2025-08-25','2025-12-25','2025-12-26'
);
but i want to avoid this if possible as it requires trusting the user to go to the foot of the script.
答案1
得分: 1
如前所述，varchar 变量只是一串数据，不会自动变成一个列表。
不要玩弄字符串拆分和转换，只需使用表变量或表值参数。同时，使用明确的日期格式，例如 yyyyMMdd 或 yyyy-MM-dd HH:mm:ss。
DECLARE @dateList AS TABLE (value date PRIMARY KEY);
INSERT @dateList (value) VALUES
('20240101'),
('20240329'),
('20240401'),
('20240506'),
('20240527'),
('20240826'),
('20241225'),
('20241226'),
('20250101'),
('20250418'),
('20250421'),
('20250505'),
('20250526'),
('20250825'),
('20251225'),
('20251226');
然后
UPDATE days
SET PublicHoliday = 1
WHERE days.Date IN
(
SELECT value FROM @dateList
);
英文:
As mentioned, a varchar variable is just a string of data, it doesn't automatically become a list.
Instead of messing around with splitting strings and conversion, just use a table variable or a Table Valued Parameter. Also use an unambiguous date format such as yyyyMMdd or yyyy-MM-dd HH:mm:ss
DECLARE @dateList AS TABLE (value date PRIMARY KEY);
INSERT @dateList (value) VALUES
('20240101'),
('20240329'),
('20240401'),
('20240506'),
('20240527'),
('20240826'),
('20241225'),
('20241226'),
('20250101'),
('20250418'),
('20250421'),
('20250505'),
('20250526'),
('20250825'),
('20251225'),
('20251226');
And then
UPDATE days
SET PublicHoliday = 1
WHERE days.Date IN
(
SELECT value FROM @dateList
);
答案2
得分: 0
以下是您提供的文本的中文翻译：
另一个答案展示了如何安全且正确地传递日期列表。当前尝试失败的原因是字符串中包含了换行符，而不仅仅是逗号和空白。
这会导致转换错误：
select cast(value as date)
from STRING_SPLIT(@dateList,';')
为了调查这个问题，可以使用TRY_CAST来返回无效日期的NULL值。以下是结果：
select try_cast(value as date)
from STRING_SPLIT(@dateList,';')
结果如下：
2024-01-01
2024-03-29
2024-04-01
2024-05-06
2024-05-27
NULL
2024-12-25
2024-12-26
2025-01-01
NULL
2025-04-21
2025-05-05
2025-05-26
NULL
2025-12-25
2025-12-26
如果不能使用表值参数（TVP），一个相对安全的替代方法是使用JSON数组字符串，然后使用OPENJSON解析它。以下字符串可以被成功解析：
DECLARE @dateList VARCHAR(MAX) = '[\"2024-01-01\", \"2024-03-29\", \"2024-04-01\", \"2024-05-06\", \"2024-05-27\",
\"2024-08-26\", \"2024-12-25\", \"2024-12-26\", \"2025-01-01\",
\"2025-04-18\", \"2025-04-21\", \"2025-05-05\", \"2025-05-26\",
\"2025-08-25\", \"2025-12-25\", \"2025-12-26\"]';
select MyDate
from OPENJSON(@dateList) with (MyDate date '$')
这将产生以下结果：
MyDate
----------
2024-01-01
2024-03-29
2024-04-01
2024-05-06
2024-05-27
2024-08-26
2024-12-25
2024-12-26
2025-01-01
2025-04-18
2025-04-21
2025-05-05
2025-05-26
2025-08-25
2025-12-25
2025-12-26
这仍然不如表值参数（TVP）好，并且占用更多的实际空间。
英文:
The other answer shows how to safely and properly pass a list of dates. The reason the current attempt fails is that the string contains newlines, not just commas and whitespace.
This fails with a conversion error:
select cast(value as date)
from STRING_SPLIT(@dateList,',')
To investigate this, TRY_CAST can be used to return NULL for invalid dates. The result of :
select try_cast(value as date)
from STRING_SPLIT(@dateList,',')
is
2024-01-01
2024-03-29
2024-04-01
2024-05-06
2024-05-27
NULL
2024-12-25
2024-12-26
2025-01-01
NULL
2025-04-21
2025-05-05
2025-05-26
NULL
2025-12-25
2025-12-26
If a TVP can't be used, a safe-ish alternative is to use a JSON array string and parse it with OPENJSON. The following string gets parsed without problems:
DECLARE @dateList VARCHAR(MAX) = '["2024-01-01", "2024-03-29", "2024-04-01", "2024-05-06", "2024-05-27",
"2024-08-26", "2024-12-25", "2024-12-26", "2025-01-01",
"2025-04-18", "2025-04-21", "2025-05-05", "2025-05-26",
"2025-08-25", "2025-12-25", "2025-12-26"]';
select MyDate
from OPENJSON(@dateList) with (MyDate date '$')
This produces
MyDate
----------
2024-01-01
2024-03-29
2024-04-01
2024-05-06
2024-05-27
2024-08-26
2024-12-25
2024-12-26
2025-01-01
2025-04-18
2025-04-21
2025-05-05
2025-05-26
2025-08-25
2025-12-25
2025-12-26
That's still not as good, and takes more actual space than a TVP.
答案3
得分: -1
DECLARE @dateList VARCHAR(MAX) = '2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27, 2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01, 2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26, 2025-08-25, 2025-12-25, 2025-12-26';
DECLARE @dates TABLE (dt DATE)
INSERT INTO @dates
SELECT DATEFROMPARTS(LEFT(dt,4), SUBSTRING(dt, 6,2), SUBSTRING(dt, 9,2)) as date
FROM
(SELECT TRIM(value) as dt FROM string_split(@dateList, ',')) val
SELECT * FROM @dates
英文:
Create a table of values like this. Update with this table.
DECLARE @dateList VARCHAR(MAX) = '2024-01-01, 2024-03-29, 2024-04-01, 2024-05-06, 2024-05-27, 2024-08-26, 2024-12-25, 2024-12-26, 2025-01-01, 2025-04-18, 2025-04-21, 2025-05-05, 2025-05-26, 2025-08-25, 2025-12-25, 2025-12-26';
DECLARE @dates TABLE (dt DATE)
INSERT INTO @dates
SELECT DATEFROMPARTS(LEFT(dt,4), SUBSTRING(dt, 6,2), SUBSTRING(dt, 9,2)) as date
FROM
(SELECT TRIM(value) as dt FROM string_split(@dateList, ',')) val
SELECT * FROM @dates
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论