May 21, 2013 at 9:45 pm
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 🙂
May 21, 2013 at 11:13 pm
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.
May 21, 2013 at 11:46 pm
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.
May 22, 2013 at 12:15 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply