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 in a database

dear experst:

i have a web site and a calendar where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

this problem is realy annoying me and i can not continue my work without solving it. any help will be more than appreciated.
thanksvarname1 = startday
varname2 = endday|||thanks for your reply,
what i want is how to know the start date and the end date when i user select a week or a month and how to calculate the value so when give it to the stored procedure it will know that is a week?
thanks|||to get the Frist/Last day-of-the-week, Frist/Last day-of-the-month u can use the following queries. this assumes that Sunday is the first day of the week and Saturday is that last one. also the dates r calculated with respect to date variable.

declare @.DtVar smalldatetime
set @.DtVar = '2007-03-22' -- an example date
select @.DtVar+1-datepart(dw,@.DtVar) as StartingDayOfTheWeek
select @.DtVar+7-datepart(dw,@.DtVar) as LastDayOfTheWeek
select @.DtVar+1 - datepart(dd,@.DtVar) as FristDateOfTheMonth
select dateadd(mm,1,@.DtVar) - datepart(dd,@.DtVar) as LastDateOfTheMonth|||thanks a lot for your answer, but what if i want monday be the first day of the week and sunday the last day of the week? what should i changed.
thanks|||dude, please play around with the number. when you got a solution for a problem, you shall learn to understand it and explore further. By asking, "what if i want Monday", you're asking the same question...|||Set @.@.datefirst 1|||thanks a lot for your answer, but what if i want monday be the first day of the week and sunday the last day of the week? what should i changed.
thanks

You can assign the days into an int array and pass to your stored procedure.

Date functions

Hi all,

Are there a functions for last day of the month or first day of the month?

Thanks in advance,

Jen

--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004Hi, Jenny!
You wrote on Fri, 21 May 2004 12:58:00 +0100:
J> Are there a functions for last day of the month
dateadd(month,1,dateadd(day,-day(getdate())+1,getdate()))
J> or first day of the month?
dateadd(day,-day(getdate())+1,getdate())

J> Thanks in advance,
NE ZA CHTO, ZAKHODITE ESCHYO!

J> Jen

-
exexe!|||"Jenny" <jennyysplace@.eidosnet.co.uk> wrote in message news:40adef3e@.212.67.96.135...
> Hi all,
> Are there a functions for last day of the month or first day of the month?
> Thanks in advance,
> Jen
>
> --
> Fast Track On Line -Web Design and Development
> Portfolio http://www.fasttrackonline.co.uk
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.688 / Virus Database: 449 - Release Date: 18/05/2004

Last day of month:

SELECT DATEADD(MONTH, 1, CURRENT_TIMESTAMP) -
DAY(DATEADD(MONTH, 1, CURRENT_TIMESTAMP))

First day of month:

SELECT CURRENT_TIMESTAMP - DAY(CURRENT_TIMESTAMP) + 1

--
JAG

date function in C#

hi experts,

i'm working in a web page for some statistics and i have a calendar where the customer can choose a day a week or a month and according to the date he select i need to query the database according to the date selected.

what i want to know is how can i store the date for a day in a variable so i can call it from a stored procedure, the day actually is easy what i want is how can i store the whole week in a variable so i can give it to the stored procedure and query the data in the database according to the whole week may be with startday and endday

also the same problem for the whole month, any idea how can i implement that in C#?!!!

thanks

Are you asking how would you query by a date range? such as a week?

-c

|||

thanks for your reply, what i wnat is how can i store the value of the whole week and month in a variable. i know how to query the database. the situation is as follow:

i have a web site where a client can select a day week or a month to see some statistics, for the day i know how to do it. and i have also stored procedures. what i want is how to store the startday and end day in a variable when the client select a week or a month, so i can assign these variables to the stored procedure so the stored procedure can query the database and return the data for just the specific selected date.

i hope i made my self clear.

any help will be appreciated.

thanks

|||

mrjoka

Check out the article below and look for the sample function called RunStoredProcParams here you will see how to return a datareader from a stored procedure that accepts a parameter

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

HTH

|||

thanks for the link, actually what i want is how to store the date value of the selected week or month from a callendar in a variable so i can assing them to a stored procedure. i know how to assign parameters to SP but how can i store the startdate and enddate selected from the user from the callendar? for example if the user select a week or a month for a day i can use :date1 = Calendar1.SelectedDate.ToShortDateString(); but for a week or a month!!!!

this is what makes me crazy these days

thanks in advance

|||

Since the SelectedDate is a DateTime variable you can do this for month;

int SelectedMonth = Calendar1.SelectedDate.Month;

The week can be done as follows

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture;int SelectedWeek = cal.GetWeekOfYear;

|||

thanks for the reply it realy helps but when i tried the week i had these fouts:

Error 1 Cannot implicitly convert type 'System.Globalization.CultureInfo' to 'System.Globalization.Calendar'

Error 2 Cannot convert method group 'GetWeekOfYear' to non-delegate type 'int'. Did you intend to invoke the method?

any idea why?

thanks again

|||

yes, I forgot the .Calendar on the CultureInfo - so its = System.Globalization.CultureInfo.CurrentCulture.Calendar;

System.Globalization.Calendar cal = System.Globalization.CultureInfo.CurrentCulture.Calendar;

That will fix both issues.

-c

|||

thanks for the quick reply,

i did wat you told me and i get rid of the first fout but the second one is still there

any idea

thanks

|||

my bad - you need to add () to the call;

int SelectedWeek = cal.GetWeekOfYear();

|||

sorry to bother you again,

here is the fout this time:

Error 1 No overload for method 'GetWeekOfYear' takes '0' arguments

is it because i'm using it inside a case statement?!!!

thanks

|||

here you go;

int SelectedWeek = System.Globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(Calendar1.SelectedDate, System.Globalization.CalendarWeekRule.FirstFullWeek, DayOfWeek.Monday);

-c|||

do i need to declareDayOfWeek because it gave me this error:

Error 1 The name 'DayOfWeek' does not exist in the current context

thanks

|||

thanks man it works like a charm

|||

DayOfWeek is under System and is an enumeration so it should be fine. Maybe try System.DayOfWeek.

-c

Date Formats and local IE settings

We have reports available on the web that take begin and end dates in US
format (i.e. 08/02/2004 for August 2, 2004). When our international
customers try to render the report the dates get converted to the date
setting they have on their local IE, so the date then looks like 02/08/2004
for August 2, 2004. The report then pulls back data for February 8, 2004.
Does anyone know of a work around for this?SP1 solves this with rs:ParameterLanguage URL access parameter. See:
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
Just install SP1 and it will work, you don't need to do any changes in the
reports.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
> We have reports available on the web that take begin and end dates in US
> format (i.e. 08/02/2004 for August 2, 2004). When our international
> customers try to render the report the dates get converted to the date
> setting they have on their local IE, so the date then looks like
02/08/2004
> for August 2, 2004. The report then pulls back data for February 8, 2004.
> Does anyone know of a work around for this?
>|||Thanks for the quick response... I didn't know about that....
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OzE4e4leEHA.3928@.TK2MSFTNGP11.phx.gbl...
> SP1 solves this with rs:ParameterLanguage URL access parameter. See:
>
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
> Just install SP1 and it will work, you don't need to do any changes in the
> reports.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
> > We have reports available on the web that take begin and end dates in US
> > format (i.e. 08/02/2004 for August 2, 2004). When our international
> > customers try to render the report the dates get converted to the date
> > setting they have on their local IE, so the date then looks like
> 02/08/2004
> > for August 2, 2004. The report then pulls back data for February 8,
2004.
> > Does anyone know of a work around for this?
> >
> >
>|||We tried setting rs:ParameterLanguage=en-US in the URL so international
users dates would stay in the MM/DD/YYYY format but it isn't working. We
have SP1 installed. Do you have any suggestions? Please help, our
customers in the UK would like to be able to view their reports. Thanks!
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OzE4e4leEHA.3928@.TK2MSFTNGP11.phx.gbl...
> SP1 solves this with rs:ParameterLanguage URL access parameter. See:
>
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
> Just install SP1 and it will work, you don't need to do any changes in the
> reports.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
> news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
> > We have reports available on the web that take begin and end dates in US
> > format (i.e. 08/02/2004 for August 2, 2004). When our international
> > customers try to render the report the dates get converted to the date
> > setting they have on their local IE, so the date then looks like
> 02/08/2004
> > for August 2, 2004. The report then pulls back data for February 8,
2004.
> > Does anyone know of a work around for this?
> >
> >
>|||I assume you are talking about the date format in the parameters area. You
need to make sure the report language (a report property) is set to english
as well. Otherwise, it will adapt to the user locale.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:eef80tneEHA.3028@.TK2MSFTNGP12.phx.gbl...
> We tried setting rs:ParameterLanguage=en-US in the URL so international
> users dates would stay in the MM/DD/YYYY format but it isn't working. We
> have SP1 installed. Do you have any suggestions? Please help, our
> customers in the UK would like to be able to view their reports. Thanks!
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OzE4e4leEHA.3928@.TK2MSFTNGP11.phx.gbl...
>> SP1 solves this with rs:ParameterLanguage URL access parameter. See:
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_new_url_access_parameters
>> Just install SP1 and it will work, you don't need to do any changes in
>> the
>> reports.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Jessica C" <jesscobbe@.hotmail.com> wrote in message
>> news:%23ORisPleEHA.592@.TK2MSFTNGP11.phx.gbl...
>> > We have reports available on the web that take begin and end dates in
>> > US
>> > format (i.e. 08/02/2004 for August 2, 2004). When our international
>> > customers try to render the report the dates get converted to the date
>> > setting they have on their local IE, so the date then looks like
>> 02/08/2004
>> > for August 2, 2004. The report then pulls back data for February 8,
> 2004.
>> > Does anyone know of a work around for this?
>> >
>> >
>>
>

Date Format Problem - SQL Server Insert From Web Application

Hi,
I've written a short aspx file so that end users can insert lines into our
SQL server database. The following string is sent by Internet Explorer to
the database where it updates the relevant table -
INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
'8', '01/05/2005 08:54:10', 'HOME\username')
The 2nd date is sent as a string into an nvarchar field so it causes no
problems but the first (which is heading for a datetime field) is assumed by
SQL to be MM/dd/yyyy format no matter what I try to do. All regional
settings are set to UK English & the table in SQL correctly uses dd/MM/yyyy
format so the above insert command fails thinking that I'm trying to add a
date of the 5th of month 15.
If I try input a date as mm/dd/yyyy format into the aspx page, an error is
thrown back.
Any ideas as to what's going wrong ?
Thanks
SteveYou can set date in an universal format 'yyyymmdd',
or use convert(datetime,'dd/mm/yyyy',103) function, see MS SQL Help or
http://www.karaszi.com/SQLServer/info_datetime.asp
Vlastik
"Steve" <steve.henderson@.btinternet.c0m> pe v diskusnm pspvku
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Steve
Always use 'YYYYMMDD' to insert data into SQL Server table. To display dates
use FORMAT or other functions to format to be suitable to the client.
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
by
> SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
dd/MM/yyyy
> format so the above insert command fails thinking that I'm trying to add a
> date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Date formats are set on the connection level in SQL Server, not database or
server wide. If they are not explicitly set, they are derived from the
default settings for the login that uses the connection. It appears to me
that the login you use to connect to the database has it's language (which
also includes the date format)set to British, but the login that you web app
uses to connect to the database, has it language set to English, i.e. U.S.
English.
Jacco Schalkwijk
SQL Server MVP
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||I suggest that you read up about SQL Code Injection once you have this
resolved. The implication from your question is that data is more or less
coming off a web form straight into the database and as such may be highly
vulnerable to hacking.
"Steve" <steve.henderson@.btinternet.c0m> wrote in message
news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've written a short aspx file so that end users can insert lines into our
> SQL server database. The following string is sent by Internet Explorer to
> the database where it updates the relevant table -
> INSERT INTO Tbl_Manual([Data Date],[Staff ID], Flag1, Flag2, Flag3, Ref1,
> Ref2, Timestamp, Inputter) values('15/05/2005', '89', '1', '0', '0', '77',
> '8', '01/05/2005 08:54:10', 'HOME\username')
> The 2nd date is sent as a string into an nvarchar field so it causes no
> problems but the first (which is heading for a datetime field) is assumed
> by SQL to be MM/dd/yyyy format no matter what I try to do. All regional
> settings are set to UK English & the table in SQL correctly uses
> dd/MM/yyyy format so the above insert command fails thinking that I'm
> trying to add a date of the 5th of month 15.
> If I try input a date as mm/dd/yyyy format into the aspx page, an error is
> thrown back.
> Any ideas as to what's going wrong ?
> Thanks
> Steve
>|||Thanks Jacco - You got it in one. You have no idea how long I've been trying
to fix this!!!
I'd set a local account up on the server for testing things - trust
Microsoft to default things to 'english' which being from England myself I
would have assumed to be correct rather than having to choose 'British
English' !
Steve
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u39qoyjTFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Date formats are set on the connection level in SQL Server, not database
> or server wide. If they are not explicitly set, they are derived from the
> default settings for the login that uses the connection. It appears to me
> that the login you use to connect to the database has it's language (which
> also includes the date format)set to British, but the login that you web
> app uses to connect to the database, has it language set to English, i.e.
> U.S. English.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Steve" <steve.henderson@.btinternet.c0m> wrote in message
> news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||>
> I'd set a local account up on the server for testing things - trust
> Microsoft to default things to 'english' which being from England myself I
> would have assumed to be correct rather than having to choose 'British
> English' !
>
LOL|||The original poster might consider a parameterized query. This will address
both the SQL injection security issue as well as date string formatting.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mercury" <me@.spam.com> wrote in message
news:d52dsi$ac7$1@.lust.ihug.co.nz...
>I suggest that you read up about SQL Code Injection once you have this
>resolved. The implication from your question is that data is more or less
>coming off a web form straight into the database and as such may be highly
>vulnerable to hacking.
>
> "Steve" <steve.henderson@.btinternet.c0m> wrote in message
> news:uBIy7ljTFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||> The original poster might consider a parameterized query. This will
> address both the SQL injection security issue as well as date string
> formatting.
I may go on to look at that but I'm currently not too bothered about hacking
attempts as the form is for intranet use only - I just need something quick
& dirty !
Thanks
Steve

Date format passing parameter to WebService

Hi Lads,
I'm having this wee problem with the date format of a parameter - I'm
building a list of the needed parameters on a web page (determined from the
report) via the API, I have set the report to English(Ireland), I assumed
this would take care of the date format (dd/MM/yyyy) rightly or wrongly I'm
not too sure - reigonal settings on my PC are the same. The problem that I
have is that when I pass back the date 30/09/2005 it comes back with an error
- any suggestions? they would be most appreciated.You can use strings instead DateTime, and you will avoid any TimeZone
convertion and reginal settings.
Jerry
http://www.rdlcomponents.com
"Rowan Massey" wrote:
> Hi Lads,
> I'm having this wee problem with the date format of a parameter - I'm
> building a list of the needed parameters on a web page (determined from the
> report) via the API, I have set the report to English(Ireland), I assumed
> this would take care of the date format (dd/MM/yyyy) rightly or wrongly I'm
> not too sure - reigonal settings on my PC are the same. The problem that I
> have is that when I pass back the date 30/09/2005 it comes back with an error
> - any suggestions? they would be most appreciated.|||Hi Jerry,
Unfortunately I can't use strings! I actually build up parameters on the web
page depending on their type in the report itself! i.e. for datetime
parameters, I create a text box an image control next to it to popup a date
selection screen, for a string I create a textbox only etc. I'm trying to
make it as generic as possible so I only have to do this once!
"Jerry" wrote:
> You can use strings instead DateTime, and you will avoid any TimeZone
> convertion and reginal settings.
> Jerry
> http://www.rdlcomponents.com
> "Rowan Massey" wrote:
> > Hi Lads,
> >
> > I'm having this wee problem with the date format of a parameter - I'm
> > building a list of the needed parameters on a web page (determined from the
> > report) via the API, I have set the report to English(Ireland), I assumed
> > this would take care of the date format (dd/MM/yyyy) rightly or wrongly I'm
> > not too sure - reigonal settings on my PC are the same. The problem that I
> > have is that when I pass back the date 30/09/2005 it comes back with an error
> > - any suggestions? they would be most appreciated.

Date Format Issue

Hello,

I have a web application that makes a simple Select query to a SQL 2000 database. This application is hosted on a load balanced farm, where each web server is supposed to be configured identically -- same code base, same iis configuration, same connection strings, etc.

My problem: When I execute the query on one of the servers in the farm, the date that gets returned includes a leading zero in the month, e.g.'08/26/2005'. The rest of the servers, executing the same query from the same database, does not include the leading zero, e.g.'8/26/2005'

What about the one server is different? Any insights would be much appreciated!

Check system settings for long and short date format.

Thanks

|||

Thanks for the quick turnaround! My dumb followup questions are:

Where do I review this information? I think I can find it in the registry under HKCU\Control Panel\International, but is that the best place to make a change, or is there a Windows-provided interface?

|||

Regional and language option in control panel

Thanks

Date format independant of local settings

Hello,
how can we make the Date format for SQLServer7.0 independant of locale settings. We have a Web Server and a database server running multiple projects having different requirement for date formats viz mm/dd/yyyy and dd/mm/yyyy.

I suppose SQLServer takes the database local date format as the current date format. The problem can be solved if i can indicate in my SQL queries the format being used in the query. This also eliminates any accidental change in database server format (from mm/dd/yyyy to dd/mm/yyyy), which would mean 1st Feb being inserted into the database as 2nd Jan.

Any Ideas !!!

Regards,

AshutoshHave you looked at SET DATEFORMAT

SET DATEFORMAT mdy
GO
DECLARE @.datevar datetime
SET @.datevar = '12/31/98'
SELECT @.datevar
GO

SET DATEFORMAT ydm
GO
DECLARE @.datevar datetime
SET @.datevar = '98/31/12'
SELECT @.datevar
GO

SET DATEFORMAT ymd
GO
DECLARE @.datevar datetime
SET @.datevar = '98/12/31'
SELECT @.datevar
GO|||Hello,
thanks the problem seems to be solved by using the SET DATEFORMAT command. While ADODB connection object we have to execute the above command as a action query -
Connection.Execute "SET DATEFORMAT dmy"

The connection thus follows this new date format.

Thanks Again :-)

Regards,

Ashutosh|||This is only a problem with character date formats.
If you always transfer dates with format yyyymmdd then you should never have a problem.

dd mmm yyyy also works as long as you don't use other languages.

date format cannot be saved

Hi guys, I was doing some changes on my asp web pages, I added new field expiry date that will be scanned into the system in format ddmmyyyy but it can't save as i am gettting that message that char can't be converted into datetime as date time was out of range.

I have added this into my asp pages <% Session.LCID = 3081 %> and changed the sql server regional setting setting the date to dd/mm/yyyy I can't set it to ddmmyyyy as there is not option without "/".

Could somebody help me as this is quite frustrating.

ThanksBefore you INSERT the data, change the format. That's fairly easy to do if these are asp pages.

Date comparison problem

Hi

I have a table that contains information that has start dates and end
dates. They are stored in short date format.

I have built a web page that initially returned all the information. I
then want to return information spcific to todays date, where I used

dim todDate
todDate = now

then I did

shTodDate = FormatDateTime(todDate, 2)

which returned the date to a web page that was in the same format as
what was in the access table.

SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"

This was fine beacuse it returned all events that started on that day.
This doesn't help though because some events last a few days, so I
tried the following

SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
endDate<=#'&shTodDate&'#;

This still only returned events that started on the current day.

Does anyone know how I can use <= or >= when comparing dates?

Thanks
puREp3s+puREp3s+ (colin42@.btinternet.com) writes:
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?

Maybe the folks over in comp.datatabases.ms-access knows? It seems
that you are using Access, from the syntax. At least it is not MS
SQL Server.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It looks like you have your comparison operators backwards. In TSQL syntax,
this is what you would do to get any events in progress at this moment:

DECLARE @.t DATETIME
SET @.t = CURRENT_TIMESTAMP

SELECT *
FROM Events
WHERE stDate <= @.t AND endDate >= @.t;

... or, if the end date may be NULL, then:

SELECT *
FROM Events
WHERE stDate <= @.t AND (endDate >= @.t OR endDate IS NULL);

Hope that helps,
Rich

"puREp3s+" <colin42@.btinternet.com> wrote in message
news:7a6f18a3.0310211330.7ad2a1d0@.posting.google.c om...
> Hi
> I have a table that contains information that has start dates and end
> dates. They are stored in short date format.
> I have built a web page that initially returned all the information. I
> then want to return information spcific to todays date, where I used
> dim todDate
> todDate = now
> then I did
> shTodDate = FormatDateTime(todDate, 2)
> which returned the date to a web page that was in the same format as
> what was in the access table.
> SELECT * from Events WHERE stDate = # ' & shTodDate & ' #;"
> This was fine beacuse it returned all events that started on that day.
> This doesn't help though because some events last a few days, so I
> tried the following
> SELECT * FROM Events WHERE stDate >=#'&shTodDate&'# AND
> endDate<=#'&shTodDate&'#;
> This still only returned events that started on the current day.
> Does anyone know how I can use <= or >= when comparing dates?
> Thanks
> puREp3s+

Date Compare in SQL Server Stored Procedure

Hi,

i have "req_date" column of "datetime" type in Database table besides
other columns.

From my Web page, i am calling the Stored Procedure with variable
parameter "Search_Date" of Varchar(60) type.

the value, i am passing to Stored procedure through "Search_Date" is
compared to req_date column of table.

My question is that how to do this comparision of date in WHERE part of
Select statement within Stored Procedure?

ThanksWhy are you using a varchar parameter when the correct data type is
datetime? I would fix the front end to use the proper data type, rather
than hack in something on the server side - it will also help to
prevent invalid data and SQL injection attacks.

If you really want to do it on the server, you can CAST() or CONVERT()
to datetime, but that's a workaround, not a solution.

Simon|||Use a DATETIME parameter rather than a VARCHAR otherwise you'll just
have to convert the value. Don't rely on implict conversion for date
strings because it is subject to regional settings on the server and
the connection.

Assuming @.search_date is a DATETIME:

WHERE req_date = @.search_date

or, if you just what to search on the date and ignore the time:

WHERE req_date >= @.search_date AND req_date <
DATEADD(DAY,1,@.search_date)

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Use a DATETIME parameter rather than a VARCHAR otherwise you'll just
> have to convert the value. Don't rely on implict conversion for date
> strings because it is subject to regional settings on the server and
> the connection.

It's worth pointing out here that the regional settings that David
are talking about are *not* those of Windows. Instead SQL Server
has its own settings that can affect how date literals are interpreted.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Date and Time Best Practice - storage in SQL Server database

With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?

For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.

My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?

Thanks in advance for your help!

PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.

Hey, this is an old song.

My point is that you have to store date in a universal format - UTC, this will prevent any troubles while moving database on another zone.

Next, your users have to setup theirs profile and specify in what timezone they are in. According to this you're making shifts.

|||

E.g. look at this site, each user has Site Option section where he/she can setup time zone.

|||

I guess it is that simple.

Do you know of any webservice or other source that has time zone data?

Thanks.

|||

Here is a good article on the subject:

http://aspnet.4guysfromrolla.com/articles/081507-1.aspx