does SQL Server consider culture/locale when converting values to/from strings?

Question

I'm attempting to update a large codebase to properly specify the CultureInfo and/or IFormatProvider when formatting/parsing values. For instance, when parsing a value I get from the user, I pass CultureInfo.CurrentCulture when calling TryParse, and when converting a float to a string for persistence, I pass CultureInfo.InvariantCulture when calling ToString.

My question is this: when generating SQL queries, should I format numbers and the like using the invariant culture, or the SQL server's culture, or what? Which is to say, if my computer is set to German (Germany), which of these queries is right?

select foo from bar where baz = 123.45

or

select foo from bar where baz = 123,45

Likewise, if I use SQL's CAST to convert a floating-point value to a string, what locale is SQL going to use for the conversion?

I did search the SQL docs, but so far I can't find any good answers. I did find some info about date formatting (SET DATEFORMAT and the like), but that's it.

NOTE: I realize that the preferred way to pass inputs to a SQL query is via parameters, so let's assume for argument's sake that I have a good reason to format them into the query string. Also, handing query inputs is only part of the broader question.

Answer 1

It will throw an error. The language of SQL Server only affects date/time values.

SET LANGUAGE Deutsch
SELECT 'true' WHERE 1 <> 123,45

--gives
Msg 102, Level 15, State 1, Line 2
Falsche Syntax in der Nähe von ','.
There is far too much ambiguity in number formats

SET LANGUAGE Deutsch
SELECT 'true' WHERE 1 <> 123.45
--gives true

I'm in Switzerland and have to deal with all 3 of these...

• 1.234,56 (DE)
• 1'234,56 (CH)
• 1,234.56 (EN-GB)