ISDATE difference from client and server

  • Hi,

    Can anyone help out me please.

    If I run select isdate('27/1/2011') from my local install of SQL against a server I get 1

    If I run the same query directly on the server I get 0

    Am I correct in assuming that there is a difference in the set up of my client to the set up of the server?

    Otherwise I would have assumed that the server would have established if this was a date and returned a consistent result irrespective of local setup.

    Thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Sounds like regional settings are different on these two servers.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Stuart Davies (3/11/2011)


    Hi,

    Can anyone help out me please.

    If I run select isdate('27/1/2011') from my local install of SQL against a server I get 1

    If I run the same query directly on the server I get 0

    Am I correct in assuming that there is a difference in the set up of my client to the set up of the server?

    Otherwise I would have assumed that the server would have established if this was a date and returned a consistent result irrespective of local setup.

    Thanks

    Run the following on your local install and against the server. Compare the returns for the "dateformat" row. If they're different, then as Sean points out, that's the reason.

    DBCC USEROPTIONS

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for your replies.

    Unfortunately it doesn't answer the question - possibly I wasn't clear enough.

    I am running SELECT isdate('27/1/2011') from my local install of SSMS against Server1 and get 1.

    I then remote desktop to Server1 and run open SSMS and run SELECT isdate('27/1/2011') and get 0.

    The query is being run against the same server in both instances. I have checked my local and server settings for regional settings and they are both the same across the three tabs.

    I have checked Jeff's suggestion of DBCC USEROPTIONS

    This is the same where ever I run it from (British)

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • So you are saying the you open SSMS on your local machine and connect to Server1 and the IsDate check returns 1.

    Then you RDP to Server1, open SSMS and connect to Server1 and the exact same query returns different results?

    That doesn't sound possible. Either way SSMS is making a connection to sql and runs a query. It doesn't matter where that connection originates the server will behave the same way. If that is the scenario you certainly have me stumped. Maybe somebody else has an idea.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (3/16/2011)


    So you are saying the you open SSMS on your local machine and connect to Server1 and the IsDate check returns 1.

    Then you RDP to Server1, open SSMS and connect to Server1 and the exact same query returns different results?

    That doesn't sound possible.

    Got it in one - that's why I'm posting here - could not see why it was happening myself !

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Well it will certainly be interesting if somebody figures out what is happening.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • well... I'm not sure why either... but some simple debugging/troubleshooting things can narrow it down.

    1. Ensure you are connecting to the same database (be it master or defined) in both cases

    2. Ensure that both versions of SSMS are the same

    3. Ensure that both the server and client have the same date format on the local computer (who knows, it could matter to SSMS)

    4. Lastly... pray to the M$ gods to take the thorn from your flesh... if that doesn't work... bribe them...

  • I'll put this on hold for the moment - point 2 from venoym - I'm using SSMS 2008 and the server is 2005. I don't know if this would make a difference but will check it out - just need to find a PC with 2005 on it.

    Many thanks for the replies so far - will update when I have anything more

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Have checked out the possibilities listed below :-

    venoym (3/16/2011)


    well... I'm not sure why either... but some simple debugging/troubleshooting things can narrow it down.

    1. Ensure you are connecting to the same database (be it master or defined) in both cases

    2. Ensure that both versions of SSMS are the same

    3. Ensure that both the server and client have the same date format on the local computer (who knows, it could matter to SSMS)

    4. Lastly... pray to the M$ gods to take the thorn from your flesh... if that doesn't work... bribe them...

    1. Both queries run against master

    2. Check - both same service pack level as well

    3. Check - both the same

    4. Any one got any spare cash?

    I think "pub" is a good answer in these circumstances!

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • It must be the date format. Is you server set to US English and your machine to British English?

    Try the following:

    SET DATEFORMAT dmy

    SELECT ISDATE('27/1/2011')

    SET DATEFORMAT mdy

    SELECT ISDATE('27/1/2011')

    SET DATEFORMAT ymd

    SELECT ISDATE('27/1/2011')

    Edit: Maybe the default short date has been changed within 'Regional and Language options' on one of the machines.

    (On my machines I always change the short date format to YYYY-MM-DD to avoid confusion when having to deal with both American and European date formats.)

  • On the face of it I would agree with you. However I have checked for differences in SQL and OS languages on any machines I have tried this on and all date formats, long short and regional settings are the same.

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • Stuart Davies (3/17/2011)


    On the face of it I would agree with you. However I have checked for differences in SQL and OS languages on any machines I have tried this on and all date formats, long short and regional settings are the same.

    What is the result of explicitly setting the date format?

  • The date settings are connection specific, not server or workstation specific, so you have to check them at run time to see the difference.

    These are the relevant settings when you run DBCC USEROPTIONS, especially dateformat:

    Set OptionValue

    languageus_english

    dateformatmdy

    datefirst7

    For isdate to return 1 for '27/1/2011', you would need to set dateformat to dmy.

    set dateformat dmy

    select isdate('27/1/2011')

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply