ISDATE difference from client and server

Question

Post reply

ISDATE difference from client and server

Stuart Davies

SSCoach

Points: 19271
More actions
March 11, 2011 at 2:25 am

#144905

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

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

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

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/

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

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)
Intro to Tally Tables and Functions

Stuart Davies SSCoach Points: 19271 More actions · Answer 3

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 4

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/

Stuart Davies SSCoach Points: 19271 More actions · Answer 5

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

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

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/

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 7

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

Stuart Davies SSCoach Points: 19271 More actions · Answer 8

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

Stuart Davies SSCoach Points: 19271 More actions · Answer 9

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

Ken McKelvey SSCoach Points: 18484 More actions · Answer 10

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.)

Stuart Davies SSCoach Points: 19271 More actions · Answer 11

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

Ken McKelvey SSCoach Points: 18484 More actions · Answer 12

Ken McKelvey

SSCoach

Points: 18484

March 17, 2011 at 9:59 am

#1300036

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?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

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 Option Value



language	us_english

dateformat	mdy

datefirst 7

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')