CODEWITHSUNDEEP
sql-servert-sqlsql-server-collation
TheKDude
TheKDude

Reputation: 19

TSQL date strangeness - two servers, same collation, different date interpretations

We develop an industry niche software that works with SQL Server. We have a customer that upgraded their SQL Server from Version 12 to Version 15 (SQL2014 to SQL2019) and ever since then, we've had date troubles.

At first, we thought it was the Server Collation, as it was different to our specifications (someone was apparently sleeping during the upgrade). So, we installed a test server, standard install in pretty much every aspect (and we set the Collation [Latin1_General_CI_AS] correctly). Now the server at our Customer is pretty much a twin of the one we use internally - and the problem persists.

The following is a test script:

declare @x1 as datetime
set @x1 = '20231231'
--set @x1 = '2023-12-31'
--set @x1 = '12.31.2023'

select @x1

Notice @x1 - The first format works for both servers. The second and the third only works on the customers server. Also, the first two formats happen to coincide with ISO-8601 - which is double strange that the first two don't work on both servers.

Now, to expand this example:

DECLARE @T1 AS DATE
DECLARE @T2 AS DATE
DECLARE @T3 AS DATE
DECLARE @T4 AS DATE

DECLARE @nT1 AS VARCHAR(10)
DECLARE @nT2 AS VARCHAR(10)
DECLARE @nT3 AS VARCHAR(10)
DECLARE @nT4 AS VARCHAR(10)

set @nT1 = '2023-10-24'
set @nT2 = '20231024'
set @nT3 = '24.10.2023'
set @nT4 = '2023.10.24'

set @T1 = cast(@nT1 as date)
set @T2 = cast(@nT2 as date)
set @T3 = cast(@nT3 as date)
set @T4 = cast(@nT4 as date)

select @t1, @t2, @t3, @t4, @nt1, @nt2, @nt3, @nt4

The script goes through without a problem on our end. Not, however, on the customers end. The customers server is unable to cast @T3.

So ... what is going on here? The servers are pretty much identical - we explicitly installed a new test server specifically for this, and yet; no dice.

Also, I realise that using VARCHAR and DATES in the same line are a mortal sin - but thats just how our procedure parameters are defined and the boss wants to keep it that way.

Upvotes: -1

Views: 184

Answers (1)

TheKDude
TheKDude

Reputation: 19

Here a more detailed description:

MSSQL MS does locale-settings (LS) per user (login, more specifically). This includes the SQL-Commands coming from that session. We were getting errors in converting datetime formats because the LS of the SQL-login session were set to a different cultural setting than what our scripts were designed for.

The solution was to dictate the LS of the executing user in question (finding it out with [DBCC USEROPTIONS;] and then going from there either creating an explicit login with manual LS settings, setting up group policy, etc).

The LS, if not using a specific SQL-Login and not dictated by group policy, is pulled from the OS that is opening a session.

Nice to know though: When programming in C#, there is a ToShortDateString() extension (for example) that, depending on the Culture settings of the thread, formats the date in its cultural context. [https://learn.microsoft.com/en-us/dotnet/api/system.datetime.toshortdatestring?view=net-7.0].

This, coupled with how MSSQL MS handles cultural settings, is actually pretty rad.

Upvotes: 0

Related Questions