Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date reflect in profiler show different for 2 PC Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 9:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:04 AM
Points: 6, Visits: 82
Dear All,

Pardon me, if im not sure how to title my topic correct, i hope i can explain it clearly here.

Here it goes, we are using SQL 2005 and we have 2 client PC connecting to it. Both PC are using windows XP OS.

I've installed the same program for both PC, but when they try to retrieve the data from the server using a particular report, 1 PC can display the information, while the other one dont display anything.

I've performed an SQL profiler to see what went wrong.

What i gather was that 1 PC, was sending the query to the server with the date format 'May 17, 2013' while the other PC will send to the server as '2013/05/17' (btw server regional setting is DD/MM/YYYY).

I thot both date will get me a result, but when i try to run the individual query, the query with 'May 17, 2013' works fine, but the other query with '2013/05/17' will give me a 'out-of-range' error, but if i swap 05 and 17 it will return me the same value as the 1st query.

Now i was wondering how come the 2 PC, doesn't reflect the same date format, i've connected using the same login.

Appreciate if any can help me on this issue, as i've cracking my head on finding one. Any help or advise will greatly appreciated.

Cheers
Randy
Post #1455293
Posted Tuesday, May 21, 2013 11:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 1,077, Visits: 1,496
When the date is specified in ISO format (reverse date yyyy-mm-dd) SQL will interpret it correctly. It will not accept yyyy-dd-mm (day and month swapped in reverse format) as it is not a recognised format. If it is working that way, there must be some conversion going on in the application connecting to SQL, in which case it's a fault (or setting) in the client application rather than a SQL issue.
Post #1455307
Posted Tuesday, May 21, 2013 11:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:04 AM
Points: 6, Visits: 82
thanks foxxo for the reply, actually both PC are using the same front-end, with regards to application i've check with the programmer they say they dont do any setting with regards to the date during the connection, so they basically connecting as to what is the default setting.

as per what i see in the profiler, once the user start the connection to sql server they have same setting for both PC.

will there be any bearing with regards to how the client setup the regional settings or language (keyboard input) locally?

the pc that showing the data was set as 'DD/MM/YYYY', but when i see the profiler the date come out as 'May 17, 2013' instead of '17/05/2013' so im a bit confuse how the SQL set the date in the query.
Post #1455312
Posted Wednesday, May 22, 2013 12:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 1,077, Visits: 1,496
Have a read through this: http://support.microsoft.com/kb/173907?wa=wsignin1.0

The local regional setting has no affect other than how dates are displayed to the user in SQL.
Profiler (running on which machine?) is displaying the date correctly, since May 17, 2013 is the same as 17/05/2013 in DMY format. Goto Tool - Options, Tick "Use regional settings to show date..." and it will be displayed according to the machines Regional setting. I know that's not what you're after, but worth a look to understand display of dates.

The problem may be due to the way the client app converts the date when entered as a char / string. When the character date string is converted to a datetime, it defaults to the Mon dd, yyyy setting. http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k%28CAST_TSQL%29;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28MISCELLANEOUSFILESPROJECT%29;k%28DevLang-TSQL%29&rd=true
The client application should have a date picker/calender and state clearly how the parameter should be formatted. EDIT: the client app may be defaulting to mm/dd/yyyy if there is no format specified, however, from what you say, if the app is configured exactly the same on both client machines, then regional settings are the only source which could cause an issue, however that issue is not due to SQL, but rather the client application's handling of date conversion for the parameter.
Post #1455319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse