Use a variable containing a string of dates in an update statement in SQL

Question

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:

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.

Answer 1

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

Answer 2

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

Answer 3

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.