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.

IN (@dateList) doesn't do what you think it does; it would be equivalent to = @dateList You can't just dump a scalar variable into an IN and expect SQL Server to treat is like a tuple. Use a table type variable/parameter or temporary table. — Thom A
– Thom A ♦, Commented Jul 4, 2023 at 12:36
Also yyyy-MM-dd is not unambigious with the datetime datatype. What happens with your STRING_SPLIT solution if you use an unambiguous format (yyyyMMdd)? — Thom A
– Thom A ♦, Commented Jul 4, 2023 at 12:38
That's just a string, not dates. Splitting on , means that all values except one will have a leading space character, eg ' 2023-01-01' instead of '2023-01-01'. The safe option is to use a table-valued parameter of date values. Another option is to use a JSON array with a WITH clause that defines the values as dates, eg select MyDate from openjson('["2023-12-31","2022-02-02"]') with (MyDate date '$') — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 4, 2023 at 12:50
The IN(@datleList) was just me clutching for straws after the previous attempts failed, but thought i'd put it on here to show everything i tried. I also tried the yyyyMMdd example and got the same error, but I wasn't aware of the ambiguity, so thanks for explaining that — Kevin Murray
– Kevin Murray, Commented Jul 4, 2023 at 12:50
The earlier formatting removed all the spaces (after a bit of trial and error), so i was ending up with what I thought was the right string of 'date,date,date' but was still getting the conversion error. — Kevin Murray
– Kevin Murray, Commented Jul 4, 2023 at 12:52

Charlieface · Accepted Answer · 2023-07-04 12:38:57Z

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

answered Jul 4, 2023 at 12:38

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Murray Over a year ago

Thanks for explaining this - I got a bit fixated on trying to manipulate the string and mould it into the right shape that I forgot about doing it this way, which is much simpler and does the job quickly and efficiently. Thank you!

Panagiotis Kanavos · Accepted Answer · 2023-07-04 13:16:19Z

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.

Amit Kumar Singh · Accepted Answer · 2023-07-04 12:48:45Z

-1

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

answered Jul 4, 2023 at 12:48

Amit Kumar Singh

4,4952 gold badges11 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related