Bug – Synapse SqlPool Language and dates

We found this interesting bug on Dedicated SQL Pool when working with British style language.

If you use Dynamic SQL to change the language, then selects on the date fail with a conversion error, but prints work. Weird…

An example repro is below

DECLARE @sql nvarchar(4000)
Declare @StartDate date=convert(date, getdate())

SET @sql = 'SET LANGUAGE British'
exec (@Sql)

print @StartDate
Select @StartDate -- "Conversion failed when converting date and/or time from character string."

If you use a hard coded language statement as opposed to dynamic, then the code works as expected. Example below

DECLARE @sql nvarchar(4000)
Declare @StartDate date=convert(date, getdate())

SET LANGUAGE British

print @StartDate
Select @StartDate -- "Conversion now works"

Notes

1. The code above corrupts the date handling in the “session” so you need to restart a new connection to get a repro
2. If you use a hard coded SET LANGUAGE then the dynamic SQL no longer corrupts the date handling in the session.
3. Despite being mentioned in the documentation use of setting language by variable doesn’t seem to work on a Synapse SqlPool, but it does on a SqlDB
   https://docs.microsoft.com/en-us/sql/t-sql/statements/set-language-transact-sql?view=sql-server-ver16