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"
- 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