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
- The code above corrupts the date handling in the “session” so you need to restart a new connection to get a repro
- If you use a hard coded SET LANGUAGE then the dynamic SQL no longer corrupts the date handling in the session.
- 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