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

Date only fields in SQL Server

Does anyone know if Microsoft is planning to add a DATE only data type
to SQLServer.

I know that you could use a datetime and convert/cast or use datepart
to compare, but this can be tedious and error prone.

What is the recommended way to compare date-only fields?

eg if convert(char(11), @.date_field) = convert(getdate(), @.date_field)
-- do something??"Mystery Man" <PromisedOyster@.hotmail.com> wrote in message
news:87c81238.0402100504.7966b095@.posting.google.c om...
> Does anyone know if Microsoft is planning to add a DATE only data type
> to SQLServer.
> I know that you could use a datetime and convert/cast or use datepart
> to compare, but this can be tedious and error prone.
> What is the recommended way to compare date-only fields?
> eg if convert(char(11), @.date_field) = convert(getdate(), @.date_field)
> -- do something??

There's an article in the November 2003 edition of SQL Server Magazine about
TSQL enhancements in Yukon, according to which the answer is yes.

http://www.sqlmag.com/Articles/Inde...ArticleID=40206

As for comparing dates only, you have to use one of the options you noted
above - DATEPART() or CONVERT():

if convert(char(8), col1, 112) = convert(char(8), col2, 112)
begin
...
end

If your application only uses dates, not times, you may be able to assume
that all times are 00:00.000, in which case you can always compare datetime
values directly. But this is a potentially risky assumption, unless you're
sure that all data entry enforces this rule.

Simon|||> What is the recommended way to compare date-only fields?
> eg if convert(char(11), @.date_field) = convert(getdate(), @.date_field)
> -- do something??
I tend to use datediff:

if datediff('day',@.date1,@.date2) = 0 begin ... end|||Mystery Man (PromisedOyster@.hotmail.com) writes:
> What is the recommended way to compare date-only fields?

datecol = @.date

Most of our date columns are of the type aba_date, which is datetime,
with this rule bound to it:

CREATE RULE aba_date AS convert(char(8), @.x, 112) = @.x

And we trust our parameters to be date values.

I should add that we rarely have reason to look at getdate() to get
the current day; we get that from a parameter table, because our
system changes day when it runs its night job, which may not be at
midnight. getdate() is only used for auditing.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Date Format?

Hi all,
Can i insert a date into SQLServer using this format 10072005? (mm/dd/yyyy)
Can i reference data in a search (WHERE) using this format?
IE: WHERE startdt >=01011753 AND enddt <= 10072005
In otherwords a date that doesn't include any separators?
Is this a bad idea?
Cheers,
AdamAdam,
Prefer to use this format:
'20051006' --October 6, 2005
HTH
Jerry
PS - Thanks again Hugo ;-)
"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:OKK18ssyFHA.1264@.tk2msftngp13.phx.gbl...
> Hi all,
> Can i insert a date into SQLServer using this format 10072005?
> (mm/dd/yyyy)
> Can i reference data in a search (WHERE) using this format?
> IE: WHERE startdt >=01011753 AND enddt <= 10072005
> In otherwords a date that doesn't include any separators?
> Is this a bad idea?
> Cheers,
> Adam
>|||Yes, it is a bad idea. Please check http://www.karaszi.com/SQLServer/in...ime
.asp first and
post back if further questions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Knight" <adam@.pertrain.com.au> wrote in message news:OKK18ssyFHA.1264@.tk2msftngp13.ph
x.gbl...
> Hi all,
> Can i insert a date into SQLServer using this format 10072005? (mm/dd/yyyy
)
> Can i reference data in a search (WHERE) using this format?
> IE: WHERE startdt >=01011753 AND enddt <= 10072005
> In otherwords a date that doesn't include any separators?
> Is this a bad idea?
> Cheers,
> Adam
>

Date Diffrent between "reports" & "reportserver"

I have a problem with the dates and "datetime" picker in my report
(sqlserver 2005 sp2).
My reginal settings on the server are: "hebrew" - "Isreal".
I have a report with a datetime picker.
If I'm opening the report from: http://myserver/REPORTSERVER and I'm
writting in the datepicker textbox the value "01/04/2008", and then
click on the calander icon it opens on the on the 4th of january 2008.
If I'm opening the report from: http://myserver/REPORTS and I'm
writting in the datepicker textbox the value "01/04/2008", and then
click on the calander icon it opens on the on the 1th of april 2008.
Does anybody know this bug and how to wotk with it?
Thanks.I'm not sure which is getting what you want. Is Reports getting you want you
want?
When you say opening it from reportserver how are you doing this? Are you
using URL integration in your app? Jump to URL from another Report? Or some
other way?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"nicknack" <roezohar@.gmail.com> wrote in message
news:bad0a977-4b16-4561-a661-ac0b35437d4b@.s37g2000prg.googlegroups.com...
>I have a problem with the dates and "datetime" picker in my report
> (sqlserver 2005 sp2).
> My reginal settings on the server are: "hebrew" - "Isreal".
> I have a report with a datetime picker.
> If I'm opening the report from: http://myserver/REPORTSERVER and I'm
> writting in the datepicker textbox the value "01/04/2008", and then
> click on the calander icon it opens on the on the 4th of january 2008.
> If I'm opening the report from: http://myserver/REPORTS and I'm
> writting in the datepicker textbox the value "01/04/2008", and then
> click on the calander icon it opens on the on the 1th of april 2008.
>
> Does anybody know this bug and how to wotk with it?
> Thanks.

Date and Time in SQLServer 2005

Hello,
i heard about Date and Time as new datatypes in SQL Server 2005. But they
are still not implemented. Will they come with one of the next servicepacks
or with SQL Server 20XX in some years (or never)?
Can i do something with user defined datatype?
HelmutUser-defined types are the only option at the moment.
An almost official explanation from MS goes along the lines of "we focus on
what we do best, and leave the rest to third party tools". :) But you didn't
hear that from me... ;)
ML
http://milambda.blogspot.com/|||You might take a look at the sample "Calendar-Aware Date/Time UDTs" that
ships with the AdventureWorks samples in SQL Server 2005. If you don't have
the samples installed, you can download them from here:
http://www.microsoft.com/downloads/...&displaylang=en
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FB5ACA56-B14D-4CDD-93BE-959B5C8BE173@.microsoft.com...
> User-defined types are the only option at the moment.
> An almost official explanation from MS goes along the lines of "we focus
> on
> what we do best, and leave the rest to third party tools". :) But you
> didn't
> hear that from me... ;)
>
> ML
> --
> http://milambda.blogspot.com/

DATE AND tIME

Can anyone tell if it is possible to change the data and time of your SQL
Server or does the SQL server use the date and time from the PC clock?
SQL Server gets the current time from the operating system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dfoote" <dfoote@.napcosecurity.com> wrote in message news:u4PTnfYpEHA.556@.TK2MSFTNGP09.phx.gbl...
> Can anyone tell if it is possible to change the data and time of your SQL
> Server or does the SQL server use the date and time from the PC clock?
>
|||I will take it one step further and ask is the format taken from the Same
place(OS) or is there a way to change the Date and time Format.?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e$rG3jYpEHA.3172@.TK2MSFTNGP10.phx.gbl...
> SQL Server gets the current time from the operating system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "dfoote" <dfoote@.napcosecurity.com> wrote in message
> news:u4PTnfYpEHA.556@.TK2MSFTNGP09.phx.gbl...
>
|||> I will take it one step further and ask is the format taken from the Same
> place(OS) or is there a way to change the Date and time Format.?
What date and time format are you talking about?
http://www.aspfaq.com/
(Reverse address to reply.)

Date / Time types

Hi, I'be just started to import data into sqlserver from ms access, and it automatically stores my dates as smallint length 4. Is this ok? For display purposes I want dd/mm/yyyy. Will 4 bytes store this?I generally recommend DATETIME unless I know that the values can be stored properly in a SMALLDATETIME column. See the Using Date and Time data (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_7uzy.asp) discussion in MSDN for more details.

-PatP