Confusing behavior with dates (SQL Svr)

[Axcontrols2]

I'm in the UK and to avoid any date confusion I use command.parameters to build my sql queries.

But I've 'inherited' a few legacy apps that use this syntax

SELECT * FROM SomeTable WHERE SomeDate < '2018-12-07'

and that YYYY-MM-DD format for the date has always worked and, in that example, ought to return records < December 7. 2018.

But now, on somebody else's server it's returning dates < July 12. 2018.

I always thought that formatting the date in the query as 'YYYY-MM-DD' was region independent and the SQL Server would understand it.

So why now is this new server not understanding ISO date?

Has MS done something to pre-parse dates in strings to block injection attacks basically forcing us to use parameters (which we should have done all along anyway) but lot of legacy code needs changing now.

My own SQL Server 2017 is not affected and YYYY-MM-DD works fine. So I assume that other guy's server has some option/config setup that makes it not understand ISO dates. I'd like to reproduce the problem on my own dev server. Where would I find that switch/option? (Or is it the client side connector that's doing it?)

Sorry. Hope I've explained this clearly)

[techgnome]

Are the dates in the query hard coded like that?

-tg

[FunkyDexter]

Has MS done something to pre-parse dates in strings to block injection attacks

Highly unlikely. 1. How would the engine recognise it as a date in first place? and 2. As you say, it would break a shed-ton of application (albeit poorly coded ones).

Where would I find that switch/option?

My best bet would be somewhere in the regional settings. I can't think what it would be though.

[jmcilhinney]

Is it possible to run a query with a hard-coded date on that server to see whether it behaves the same way as your application code? That would tell you for sure whether it's something in the database server (which would be my first guess) or the ADO.NET provider. What version and edition of SQL Server are they using? What culture is set on that machine?

[Axcontrols2]

Are the dates in the query hard coded like that?

-tg

In the actual application the date is datetime variable and there's a function that converts that to a string in the 'YYYY-MM-DD' format. It's been running for around 10 years 24/7/365

When the program was switched to use a different sql server it went wrong.

So to debug it I wanted to eliminate the function that converts the date to a YYYY-MM-DD string. So created a small app with only the Connection and Command objects and hard-coded the '2018-12-07' into the SELECT query and executed it. It only returned records older than July 12th.

In this one case it was just an extra line or two to make the query use a parameter instead and it works fine.

But I'm just confused.

I was wondering if MS in the svr has added a TryParse to the YYYY-MM-DD string just to make sure it's real (ie not a code injection) before passing the query into the database and that TryParse is producing the result with MM/DD the wrong way round.?

Or something like that :\

[Axcontrols2]

Is it possible to run a query with a hard-coded date on that server to see whether it behaves the same way as your application code?

I'm not on-site anymore so don't have access to their actual server.

But I'm setting up a VM with the same versions just so I can see if I can reproduce the problem.

[PlausiblyDamp]

In the actual application the date is datetime variable and there's a function that converts that to a string in the 'YYYY-MM-DD' format. It's been running for around 10 years 24/7/365

When the program was switched to use a different sql server it went wrong.

So to debug it I wanted to eliminate the function that converts the date to a YYYY-MM-DD string. So created a small app with only the Connection and Command objects and hard-coded the '2018-12-07' into the SELECT query and executed it. It only returned records older than July 12th.

In this one case it was just an extra line or two to make the query use a parameter instead and it works fine.

But I'm just confused.

I was wondering if MS in the svr has added a TryParse to the YYYY-MM-DD string just to make sure it's real (ie not a code injection) before passing the query into the database and that TryParse is producing the result with MM/DD the wrong way round.?

Or something like that :\

Do you know if the two SQL Servers have the same regional settings? I suspect the server is parsing the string based on it's regional settings, if they are different that would probably explain the issue.

[Axcontrols2]

OK. This is what I've found.

In SQL Server Management studio right click a server instance and select "Properties" and go to the "Advanced" Tab. You'll see the default language is "English".

Even in the UK the "Default Language" of a fresh installation of SQL Server is "English" (Which actually equates to US-English")

Create a user USER_A

Have that user INSERT some records.

Then go back to that advanced tab and change the Default Language to "British English"

Create another user USER_B

Now when USER_B SELECTs records by using 'YYYY-MM-DD' in the query the fun begins. It seems that if the default languages of the users is different (ie one is US and the other is UK) then it results in the query results not being what is expected.

In fact the fun continues because when the default language is changed it only affects new users so USER_A continues using US-English while USER_B is British.

I don't really understand why this occurs. I thought a date/Time was just a big number. The number of ticks since some Epoch in which case the locales of the writer and reading users shouldn't make a jot of difference because the client reads the big number and converts it to a human readable date/time in the user's locale.

But SQL Server does appear to be affected.

But only if the date is in the query as a string. Use command.Parameters and there's no problem.

[Axcontrols2]

I deleted this post because I think the one below does a better job of explaining it.

[Axcontrols2]

And here I'm just using sql server management studio to issue a query.

You can clearly see the query '>2018-12-06'

and you can clearly see that the results includes records from July !!



Just to summarize the steps in case you want to see this yourself.
1. SQL Server 2017 installed on a UK computer. There is no option during install to select a language or locale.
2. After install you will see that the default language is 'English'. (Equates to US-English)
3. Create a user.
4. Change Default language of server to 'British English'. This change only affects new user accounts.
5. Create another user.

6. First user inserts some records (First user is still 'English' because the above language change to 'British' only affects new users)
7. UK user reads those records and gets records outside of the requested date as shown above.