dcsimg
Results 1 to 10 of 10

Thread: Confusing behavior with dates (SQL Svr)

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    35

    Confusing behavior with dates (SQL Svr)

    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

    Code:
    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)

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,945

    Re: Confusing behavior with dates (SQL Svr)

    Are the dates in the query hard coded like that?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,031

    Re: Confusing behavior with dates (SQL Svr)

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,631

    Re: Confusing behavior with dates (SQL Svr)

    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?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    35

    Re: Confusing behavior with dates (SQL Svr)

    Quote Originally Posted by techgnome View Post
    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 :\

  6. #6

    Thread Starter
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    35

    Re: Confusing behavior with dates (SQL Svr)

    Quote Originally Posted by jmcilhinney View Post
    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.

  7. #7
    Fanatic Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    888

    Re: Confusing behavior with dates (SQL Svr)

    Quote Originally Posted by Axcontrols2 View Post
    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.

  8. #8

    Thread Starter
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    35

    Re: Confusing behavior with dates (SQL Svr)

    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.

  9. #9

    Thread Starter
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    35

    Re: Confusing behavior with dates (SQL Svr)

    I deleted this post because I think the one below does a better job of explaining it.
    Last edited by Axcontrols2; Dec 8th, 2018 at 06:02 AM.

  10. #10

    Thread Starter
    Member
    Join Date
    Jun 2018
    Location
    Krähental
    Posts
    35

    Re: Confusing behavior with dates (SQL Svr)

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

    Name:  pic2.jpg
Views: 21
Size:  50.4 KB

    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.
    Last edited by Axcontrols2; Dec 8th, 2018 at 06:39 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width