Date problem

Dear all
We have two machines both running SQLServer 2000 on Win2000 server boxes and
running the same web application.
Both SQLServers have, as far as I can tell, been set up identically. Both
Windows machines have, as far as I can tell, been set up identically.
We have a DATE problem with one of the machines but not the other; since
they're running exactly the same software there must be a difference
somewhere on the machine but I can't work out what the difference is. I'm
hoping someone else can...
The problem is this. The web application is supposed to display dates in
British format (dd-mm-yyyy) rather than US format (mm-dd-yyyy). However,
sometimes the application displays this in UK format and other times in US
format. We're not absolutely sure about this, but it appears that the
swapping between formats occurs when someone with ADMIN rights logs onto the
machine using either Terminal Services Client or PCAnywhere. As mentioned,
this does not happen on the other server.
The problem is not simply a display problem. Another manifestation is that
the functionality changes. The application has eCommerce functionality.
What is happening is that someone uses the eCommerce application and
attempts to buy something. It won't allow them to purchase anything because
it reckons that they've already blown their budget for that particular
month - they haven't, it's the fact that the machine's getting confused
between the day and the month. If the dates revert to UK format then they
can purchase.
Okay, I know, the application should specifically handle dates in ANSI
standard format (yyyy-mm-dd) to avoid any possible confusion, but that's
something we can't address just yet. Besides, exactly the same application
is running on another "identical" server and so presumably there's a server
setting (SQLServer or Windows?) that we just need to change. Hopefully...
Any suggestions please forward. This is turning into a situation where we
have disgruntled customers demanding we resolve it YESTERDAY.
Many thanks in advance
GriffMost probably the one logging into the machine uses some regional settings in Windows control panel
and SQL Server picks it up for some reason. The app shouldn't be made dependent in regional settings
this way, of course, but that is an issue for the app developers.
Maybe, but just maybe, you can fix this by the service account for the services. Perhaps it is using
local system and because of that picks up the settings from the logged on user?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eLY7UBAtDHA.2004@.TK2MSFTNGP10.phx.gbl...
> Dear all
> We have two machines both running SQLServer 2000 on Win2000 server boxes and
> running the same web application.
> Both SQLServers have, as far as I can tell, been set up identically. Both
> Windows machines have, as far as I can tell, been set up identically.
> We have a DATE problem with one of the machines but not the other; since
> they're running exactly the same software there must be a difference
> somewhere on the machine but I can't work out what the difference is. I'm
> hoping someone else can...
> The problem is this. The web application is supposed to display dates in
> British format (dd-mm-yyyy) rather than US format (mm-dd-yyyy). However,
> sometimes the application displays this in UK format and other times in US
> format. We're not absolutely sure about this, but it appears that the
> swapping between formats occurs when someone with ADMIN rights logs onto the
> machine using either Terminal Services Client or PCAnywhere. As mentioned,
> this does not happen on the other server.
> The problem is not simply a display problem. Another manifestation is that
> the functionality changes. The application has eCommerce functionality.
> What is happening is that someone uses the eCommerce application and
> attempts to buy something. It won't allow them to purchase anything because
> it reckons that they've already blown their budget for that particular
> month - they haven't, it's the fact that the machine's getting confused
> between the day and the month. If the dates revert to UK format then they
> can purchase.
> Okay, I know, the application should specifically handle dates in ANSI
> standard format (yyyy-mm-dd) to avoid any possible confusion, but that's
> something we can't address just yet. Besides, exactly the same application
> is running on another "identical" server and so presumably there's a server
> setting (SQLServer or Windows?) that we just need to change. Hopefully...
> Any suggestions please forward. This is turning into a situation where we
> have disgruntled customers demanding we resolve it YESTERDAY.
> Many thanks in advance
> Griff
>|||Hi
SQL Server has nothing with displaing date on the client. It is issue of
Regional Settings on your computer
Yes you are right about a 'good' format for storing date is YYYYMMDD but to
display it on the client again it is issue of Regional Settings.
Also you may want to look at SET DATEFORMAT on BOL.
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:eLY7UBAtDHA.2004@.TK2MSFTNGP10.phx.gbl...
> Dear all
> We have two machines both running SQLServer 2000 on Win2000 server boxes
and
> running the same web application.
> Both SQLServers have, as far as I can tell, been set up identically. Both
> Windows machines have, as far as I can tell, been set up identically.
> We have a DATE problem with one of the machines but not the other; since
> they're running exactly the same software there must be a difference
> somewhere on the machine but I can't work out what the difference is. I'm
> hoping someone else can...
> The problem is this. The web application is supposed to display dates in
> British format (dd-mm-yyyy) rather than US format (mm-dd-yyyy). However,
> sometimes the application displays this in UK format and other times in US
> format. We're not absolutely sure about this, but it appears that the
> swapping between formats occurs when someone with ADMIN rights logs onto
the
> machine using either Terminal Services Client or PCAnywhere. As
mentioned,
> this does not happen on the other server.
> The problem is not simply a display problem. Another manifestation is
that
> the functionality changes. The application has eCommerce functionality.
> What is happening is that someone uses the eCommerce application and
> attempts to buy something. It won't allow them to purchase anything
because
> it reckons that they've already blown their budget for that particular
> month - they haven't, it's the fact that the machine's getting confused
> between the day and the month. If the dates revert to UK format then they
> can purchase.
> Okay, I know, the application should specifically handle dates in ANSI
> standard format (yyyy-mm-dd) to avoid any possible confusion, but that's
> something we can't address just yet. Besides, exactly the same
application
> is running on another "identical" server and so presumably there's a
server
> setting (SQLServer or Windows?) that we just need to change.
Hopefully...
> Any suggestions please forward. This is turning into a situation where we
> have disgruntled customers demanding we resolve it YESTERDAY.
> Many thanks in advance
> Griff
>|||I guess it might also be down to the IIS service account?
I've checked the two machines.
On both, MSSQLServer runs as .\Administrator and IIS Admin Service runs as
LocalSystem.
Griff|||As Uri mentioned, it is likely that the regional settings are different on
the machines. Log on to each machine under the local administrator account
and check the regional settings via Control Panel. If these are the same,
it may be that another account is being used for database access. I don't
know much about IIS but I understand that the account can vary depending on
your IIS configuration.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:Oxip6SAtDHA.2340@.TK2MSFTNGP12.phx.gbl...
> I guess it might also be down to the IIS service account?
> I've checked the two machines.
> On both, MSSQLServer runs as .\Administrator and IIS Admin Service runs as
> LocalSystem.
> Griff
>
>|||"Dan Guzman" <danguzman@.nospam-earthlink.net> escribió en el mensaje
news:uFX1iTCtDHA.3492@.TK2MSFTNGP11.phx.gbl...
> As Uri mentioned, it is likely that the regional settings are different on
> the machines. Log on to each machine under the local administrator
account
> and check the regional settings via Control Panel. If these are the same,
> it may be that another account is being used for database access. I don't
> know much about IIS but I understand that the account can vary depending
on
> your IIS configuration.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
Got exactly the same problem in our domain, with two servers displaying
dates differently. The data was taken from SQL server, then displayed
through an .asp page on IIS. In one server, the dates displayed correctly
("dd/mm/yyyy"); in the other server, the dates always displayed
"mm/dd/aaaa". Yes, not even the USA "mm/dd/yyyy" but the strange
"mm/dd/aaaa". The trouble was with the Regional Settings, but it couldn't
be fixed through Control Panel, because the b0rken profile was the Default
User. I had to hack it directly over the Registry: export the branch
"HKU\<SID of Admin>\Control Panel\International", open with Notepad and
substitute "<SID of Admin>" with ".DEFAULT", save & close, then double click
to import the settings over the Default User branch. After restarting,
behaved correctly.
Daniel Diaz|||Hi Daniel
Not seen this reply before, but it's exactly what we did. The added
confusion that we had was that we were using PCAnywhere. This takes your
client settings and applies them to the server. The result was that when we
interrogated both servers, they appeared to be identical. The behaviour of
the "errant" machine changed when someone logged in using PCAnywhere (it
corrected itself) and went to US format when someone logged off PCAnywhere.
We did exactly what you said you did and now we have two truly identical
machines!
Beware PCAnywhere...
Thanks
Griff