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
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Sunday, March 25, 2012
Date portion comparison of a datetime field
I have a datetime variable coming from my ASP.NET application that has
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:
> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.
a time portion. I give my users the option to perform an equals,
greater than, less than, or between comparison. The trouble comes in
the way the application builds the criteria string. The WHERE clause
passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
What I want to do is only compare the date portion of start_dt to the
date portion of the passed in time. Manipulating the start_dt with the
built-in SQL functions isn't a problem, but altering the date passed in
from the ASP.NET would be a massive framework change in the app.
Is there any way to only compare the date portions of both the SQL
field and the passed in value?
Thanks.Create a stored procedure instead creating the statement dynamically.
create procedure dbo.usp_proc1
@.sd datetime
as
set nocount on
select c1, ..., cn
from table1
where
start_dt >= convert(char(8), @.sd, 112)
and start_dt < convert(char(8), dateadd(day, 1, @.sd), 112)
return @.@.error
go
AMB
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You have to convert it to a the valid format you want to comapre it to, e.g.
(from BOL --> Convert)
CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formatting
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.|||You need to consider using CONVERT fnc with RIGHT
or using DATEPART !
exemple :
right(convert(varchar, @.datetime, 112),10)
or
cast(datepart(hour,@.datetime) as varchar) + ':' +
cast(datepart(minute,@.datetime) as varchar) + ':' +
cast(datepart(second,@.datetime) as varchar)|||Thanks for the quick reply.
Doing the conversion on the start_dt isn't a problem, but as the value being
passed in from the app is in a dynamic string, performing some string
manipulation would be difficult as the string length could vary. The
frameworks as it stands uses dynamic criteria strings so changing that is no
t
an option. I was hoping there was a way to compare only the date portions
without too much manipulation.
"Jens Sü?meyer" wrote:
> You have to convert it to a the valid format you want to comapre it to, e.
g.
> (from BOL --> Convert)
> CONVERT(varchar(8),YourdateinHere,112) which will apply iso date formattin
g
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "colinhumber" <colinhumber@.discussions.microsoft.com> schrieb im Newsbeitr
ag
> news:AE4EA0BE-51D8-459F-A071-225F6833AFAB@.microsoft.com...
>
>|||DateDiff(day, 0, <AnyDate> ) strips off the time portion...
so
Where DateDiff(day, 0, start_dt) <Operator> DateDiff(day, 0, @.PassedInDate)
is one way to do this generically. (Happens to be really fast too.)
"colinhumber" wrote:
> I have a datetime variable coming from my ASP.NET application that has
> a time portion. I give my users the option to perform an equals,
> greater than, less than, or between comparison. The trouble comes in
> the way the application builds the criteria string. The WHERE clause
> passed in is in the format, "(start_dt = '2005/05/16 07:00:00.000')".
> What I want to do is only compare the date portion of start_dt to the
> date portion of the passed in time. Manipulating the start_dt with the
> built-in SQL functions isn't a problem, but altering the date passed in
> from the ASP.NET would be a massive framework change in the app.
> Is there any way to only compare the date portions of both the SQL
> field and the passed in value?
> Thanks.
Sunday, March 11, 2012
Date frustration
I screwed up when building my application, now I need to try to fix it...
Initially I setup my dates in a varchar field, now I need them in a datetime
field...
I have the following query that is acting funny,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
This query will not successfully run, giving me this error,
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
But, if I edit the query like this,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
WHERE EmpID = 'aa381234'
then the query will work fine... I don't want to have to run the query for
every employee in the database, so how can I do this?
Thanks,
DrewUse ISDATE() function to identify if the data in the column are truly date
values. Check out SQL Server Books Online for more details on this function.
Anith|||Hi
Check that all the values in the EmpSWVTCSepDate column are actually valid
dates.
SELECT * FROM tblEmpPersonalInfo
WHERE ISDATE(EmpSWVTCSepDate) = 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a
datetime
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>|||Thanks... There was 1 invalid date in there, 10-10--2003
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check that all the values in the EmpSWVTCSepDate column are actually valid
> dates.
> SELECT * FROM tblEmpPersonalInfo
> WHERE ISDATE(EmpSWVTCSepDate) = 0
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> datetime
>|||Drew,
1 - Update those without problem
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1
2 - Identify those with problem, correct the problem and update them again.
select * from tblEmpPersonalInfo
where isdate(EmpSWVTCSepDate) = 0
-- fix the problem
...
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1 and SepDate is null
AMB
"Drew" wrote:
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a dateti
me
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>
>|||Too bad we cannot set some sort of flashing attribute on this message :)
Glad you found your problem, and even more important, glad you are cutting
future problems off at the knee caps. The way a value displays can be dealt
with at runtime, the most important thing is that the value is true!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:ewv0xWlAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Thanks... There was 1 invalid date in there, 10-10--2003
> Drew
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
Initially I setup my dates in a varchar field, now I need them in a datetime
field...
I have the following query that is acting funny,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
This query will not successfully run, giving me this error,
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
But, if I edit the query like this,
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
WHERE EmpID = 'aa381234'
then the query will work fine... I don't want to have to run the query for
every employee in the database, so how can I do this?
Thanks,
DrewUse ISDATE() function to identify if the data in the column are truly date
values. Check out SQL Server Books Online for more details on this function.
Anith|||Hi
Check that all the values in the EmpSWVTCSepDate column are actually valid
dates.
SELECT * FROM tblEmpPersonalInfo
WHERE ISDATE(EmpSWVTCSepDate) = 0
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a
datetime
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>|||Thanks... There was 1 invalid date in there, 10-10--2003
Drew
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi
> Check that all the values in the EmpSWVTCSepDate column are actually valid
> dates.
> SELECT * FROM tblEmpPersonalInfo
> WHERE ISDATE(EmpSWVTCSepDate) = 0
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:e6aUZElAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> datetime
>|||Drew,
1 - Update those without problem
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1
2 - Identify those with problem, correct the problem and update them again.
select * from tblEmpPersonalInfo
where isdate(EmpSWVTCSepDate) = 0
-- fix the problem
...
UPDATE tblEmpPersonalInfo SET
SepDate = EmpSWVTCSepDate
where isdate(EmpSWVTCSepDate) = 1 and SepDate is null
AMB
"Drew" wrote:
> I screwed up when building my application, now I need to try to fix it...
> Initially I setup my dates in a varchar field, now I need them in a dateti
me
> field...
> I have the following query that is acting funny,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> This query will not successfully run, giving me this error,
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> But, if I edit the query like this,
> UPDATE tblEmpPersonalInfo SET
> SepDate = EmpSWVTCSepDate
> WHERE EmpID = 'aa381234'
> then the query will work fine... I don't want to have to run the query for
> every employee in the database, so how can I do this?
> Thanks,
> Drew
>
>|||Too bad we cannot set some sort of flashing attribute on this message :)
Glad you found your problem, and even more important, glad you are cutting
future problems off at the knee caps. The way a value displays can be dealt
with at runtime, the most important thing is that the value is true!
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:ewv0xWlAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Thanks... There was 1 invalid date in there, 10-10--2003
> Drew
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:u4k2MPlAFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
Date Formats
Hi,
While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
the following error :
"The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value"
But the same statement executes without any error if I connnect the
application to a different SQL Server.
I'm using SQL Server 7.
Why this error, do I need to set anything the server or the client ?
Thanks
Rajeev RIs DATEOFLEAVING of datetime datatype?
If so try the following:
set dateformat dmy
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = '08/31/2003'
or this
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = convert(datetime, '08/31/2003', 103)
--
Dean Savovic
www.teched.hr
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client ?
> Thanks
> Rajeev R
>|||Try changing the statement to:
SELECT STAFFNO FROM MASTER WHERE DATEOFLEAVING = CAST('2003-08-31 00:00:00'
AS DATETIME)
Pete
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message
news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm
getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect
the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client
?
> Thanks
> Rajeev R
>
While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
the following error :
"The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value"
But the same statement executes without any error if I connnect the
application to a different SQL Server.
I'm using SQL Server 7.
Why this error, do I need to set anything the server or the client ?
Thanks
Rajeev RIs DATEOFLEAVING of datetime datatype?
If so try the following:
set dateformat dmy
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = '08/31/2003'
or this
SELECT STAFFNO
FROM MASTER
WHERE DATEOFLEAVING = convert(datetime, '08/31/2003', 103)
--
Dean Savovic
www.teched.hr
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client ?
> Thanks
> Rajeev R
>|||Try changing the statement to:
SELECT STAFFNO FROM MASTER WHERE DATEOFLEAVING = CAST('2003-08-31 00:00:00'
AS DATETIME)
Pete
"Rajeev Ramanujan" <rajeevramanujan@.hotmail.com> wrote in message
news:%23QZaDW3SDHA.2188@.TK2MSFTNGP10.phx.gbl...
> Hi,
> While I'm trying to execute the SQL statement "SELECT STAFFNO FROM
> MASTER WHERE DATEOFLEAVING = "08/31/2003" " from my application I'm
getting
> the following error :
> "The conversion of a char data type to a datetime data type
> resulted in an out-of-range datetime value"
> But the same statement executes without any error if I connnect
the
> application to a different SQL Server.
> I'm using SQL Server 7.
> Why this error, do I need to set anything the server or the client
?
> Thanks
> Rajeev R
>
Date formats
I am currently supporting an application that runs in SQL
Server 2000. One of our customers is having a problem
with filtering items from the database using dates. The
problem is that they can only filter on dates using US
date format, ie mm/dd/yy, yet once the day exceeds 12 they
can no longer view any data from the database. So if they
filter on 09/12/03 that will work fine and pull back data
from the 12th of September. If they filter on 09/13/03 it
does not pull anything back, even though there is data
from that day. Using UK format dates does not pull any
data back at all. This does not happen for any of my
other customers who are running the same application in
SQL Server 2000. Is there an option that can be set
either on the database or on the server that can affect
this?
Any ideas would be greatly appreciated.Ideally this requires a code change in your application.
Your app needs to pass dates around in a non ambiguous
format eg :- '13 Mar 2003', this format can cause problems
if you are running multiple languages though. The safeest
bet is yyyymmdd SQL will always interpret this as ymd.
If a code change isn't possible and you want a quick fix
for this client, compare the regional settings in control
panel to the troublesome machine to a known good one. I
imagine it's running English (US) and everyone else is
using English (UK) or vice versa.
HTH
Ryan
>--Original Message--
>I am currently supporting an application that runs in SQL
>Server 2000. One of our customers is having a problem
>with filtering items from the database using dates. The
>problem is that they can only filter on dates using US
>date format, ie mm/dd/yy, yet once the day exceeds 12
they
>can no longer view any data from the database. So if
they
>filter on 09/12/03 that will work fine and pull back data
>from the 12th of September. If they filter on 09/13/03
it
>does not pull anything back, even though there is data
>from that day. Using UK format dates does not pull any
>data back at all. This does not happen for any of my
>other customers who are running the same application in
>SQL Server 2000. Is there an option that can be set
>either on the database or on the server that can affect
>this?
>Any ideas would be greatly appreciated.
>.
>
Server 2000. One of our customers is having a problem
with filtering items from the database using dates. The
problem is that they can only filter on dates using US
date format, ie mm/dd/yy, yet once the day exceeds 12 they
can no longer view any data from the database. So if they
filter on 09/12/03 that will work fine and pull back data
from the 12th of September. If they filter on 09/13/03 it
does not pull anything back, even though there is data
from that day. Using UK format dates does not pull any
data back at all. This does not happen for any of my
other customers who are running the same application in
SQL Server 2000. Is there an option that can be set
either on the database or on the server that can affect
this?
Any ideas would be greatly appreciated.Ideally this requires a code change in your application.
Your app needs to pass dates around in a non ambiguous
format eg :- '13 Mar 2003', this format can cause problems
if you are running multiple languages though. The safeest
bet is yyyymmdd SQL will always interpret this as ymd.
If a code change isn't possible and you want a quick fix
for this client, compare the regional settings in control
panel to the troublesome machine to a known good one. I
imagine it's running English (US) and everyone else is
using English (UK) or vice versa.
HTH
Ryan
>--Original Message--
>I am currently supporting an application that runs in SQL
>Server 2000. One of our customers is having a problem
>with filtering items from the database using dates. The
>problem is that they can only filter on dates using US
>date format, ie mm/dd/yy, yet once the day exceeds 12
they
>can no longer view any data from the database. So if
they
>filter on 09/12/03 that will work fine and pull back data
>from the 12th of September. If they filter on 09/13/03
it
>does not pull anything back, even though there is data
>from that day. Using UK format dates does not pull any
>data back at all. This does not happen for any of my
>other customers who are running the same application in
>SQL Server 2000. Is there an option that can be set
>either on the database or on the server that can affect
>this?
>Any ideas would be greatly appreciated.
>.
>
Thursday, March 8, 2012
Date Format Problem in SQL
Hi
My Application is designed for 5
languages.English,Spain,France,Japan and Germany.
It works fine for all other 4 languages except France.The
Problem is When Retreiving data from the database i get
an error "Arithmatic Overflow of data of datatype DateTime.
The same Application it works for france in my location
but at the vendor location it fails.we have changed all
the configuration setting(To France Setting(regional
setting and have installed the SQL Server in France as
default language) but still it works fine in our
Location.We have tried running the Application by
replicating their database, we dont face any problem here.
It will be grate ful if you provide solution for this.Is
that in the vendor site they have to install the lastest
service pack version or any thing else to do with the SQL
Server setting in France(because in all other vendor
locations(Spain,Germany,Japan) works fine.Please suggest
some solution for this.
Thanks and Regards,
SumathiHi Sumathi,
I didn't get chance of working on multiple languages.
But, the concept is this:
Installations of localized versions of SQL Server
install the translated set of messages in addition to the
English set. When SQL Server sends a message to a
connection, it uses the localized message if the language
ID of the connection matches one of the language IDs
present in sysmessages. If there is no message in
sysmessages with the same language ID, the English version
of the message is sent.
Please check the "sysmessages" and "syslanguages" and
see whether you have language ID for French.
Also, its always good to keep the all servers with same
service pack level (latest SP3).
Hope this may help you to start with debugging this issue.
Appreciate response if this suggestion helps.
-Varad
>--Original Message--
>Hi
> My Application is designed for 5
>languages.English,Spain,France,Japan and Germany.
>It works fine for all other 4 languages except France.The
>Problem is When Retreiving data from the database i get
>an error "Arithmatic Overflow of data of datatype
DateTime.
>The same Application it works for france in my location
>but at the vendor location it fails.we have changed all
>the configuration setting(To France Setting(regional
>setting and have installed the SQL Server in France as
>default language) but still it works fine in our
>Location.We have tried running the Application by
>replicating their database, we dont face any problem
here.
>It will be grate ful if you provide solution for this.Is
>that in the vendor site they have to install the lastest
>service pack version or any thing else to do with the SQL
>Server setting in France(because in all other vendor
>locations(Spain,Germany,Japan) works fine.Please suggest
>some solution for this.
>Thanks and Regards,
> Sumathi
>.
>
My Application is designed for 5
languages.English,Spain,France,Japan and Germany.
It works fine for all other 4 languages except France.The
Problem is When Retreiving data from the database i get
an error "Arithmatic Overflow of data of datatype DateTime.
The same Application it works for france in my location
but at the vendor location it fails.we have changed all
the configuration setting(To France Setting(regional
setting and have installed the SQL Server in France as
default language) but still it works fine in our
Location.We have tried running the Application by
replicating their database, we dont face any problem here.
It will be grate ful if you provide solution for this.Is
that in the vendor site they have to install the lastest
service pack version or any thing else to do with the SQL
Server setting in France(because in all other vendor
locations(Spain,Germany,Japan) works fine.Please suggest
some solution for this.
Thanks and Regards,
SumathiHi Sumathi,
I didn't get chance of working on multiple languages.
But, the concept is this:
Installations of localized versions of SQL Server
install the translated set of messages in addition to the
English set. When SQL Server sends a message to a
connection, it uses the localized message if the language
ID of the connection matches one of the language IDs
present in sysmessages. If there is no message in
sysmessages with the same language ID, the English version
of the message is sent.
Please check the "sysmessages" and "syslanguages" and
see whether you have language ID for French.
Also, its always good to keep the all servers with same
service pack level (latest SP3).
Hope this may help you to start with debugging this issue.
Appreciate response if this suggestion helps.
-Varad
>--Original Message--
>Hi
> My Application is designed for 5
>languages.English,Spain,France,Japan and Germany.
>It works fine for all other 4 languages except France.The
>Problem is When Retreiving data from the database i get
>an error "Arithmatic Overflow of data of datatype
DateTime.
>The same Application it works for france in my location
>but at the vendor location it fails.we have changed all
>the configuration setting(To France Setting(regional
>setting and have installed the SQL Server in France as
>default language) but still it works fine in our
>Location.We have tried running the Application by
>replicating their database, we dont face any problem
here.
>It will be grate ful if you provide solution for this.Is
>that in the vendor site they have to install the lastest
>service pack version or any thing else to do with the SQL
>Server setting in France(because in all other vendor
>locations(Spain,Germany,Japan) works fine.Please suggest
>some solution for this.
>Thanks and Regards,
> Sumathi
>.
>
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
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 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
Wednesday, March 7, 2012
Date Format
Im running an ASP.net application on a windows server with SQL server for
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issue
Sounds a little odd. For some reason your default date format sound like UK English.
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY format.
if that fails then look at sp_configure for the default full-text language. Make sure that it is 1033 for US English and not 2057 for UK English. That may not be the fix but I am still looking.
Jeff
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issue
Sounds a little odd. For some reason your default date format sound like UK English.
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY format.
if that fails then look at sp_configure for the default full-text language. Make sure that it is 1033 for US English and not 2057 for UK English. That may not be the fix but I am still looking.
Jeff
Saturday, February 25, 2012
Date Format
Im running an ASP.net application on a windows server with SQL server for
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issueSounds a little odd. For some reason your default date format sound like UK English
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY format
if that fails then look at sp_configure for the default full-text language. Make sure that it is 1033 for US English and not 2057 for UK English. That may not be the fix but I am still looking
Jeff
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issueSounds a little odd. For some reason your default date format sound like UK English
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY format
if that fails then look at sp_configure for the default full-text language. Make sure that it is 1033 for US English and not 2057 for UK English. That may not be the fix but I am still looking
Jeff
Date Format
Im running an ASP.net application on a windows server with SQL server for
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issueSounds a little odd. For some reason your default date format sound like UK
English.
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY
format.
if that fails then look at sp_configure for the default full-text language.
Make sure that it is 1033 for US English and not 2057 for UK English. That
may not be the fix but I am still looking.
Jeff
the database. The date format I am using is MM/DD/YYYY but that seems to
work on my local server and not on my production server. On my production
server, I get errors if the date isnt in DD/MM/YYYY. Does anyone know why or
where there is a setting to change this? I am not sure if this is a SQL or
server issueSounds a little odd. For some reason your default date format sound like UK
English.
Have you tried doing direct selects from the SQL sever using the MM/DD/YYYY
format.
if that fails then look at sp_configure for the default full-text language.
Make sure that it is 1033 for US English and not 2057 for UK English. That
may not be the fix but I am still looking.
Jeff
Friday, February 17, 2012
Date comparison problem
Hi,
If for some reason you can't get the back-end logic to work correctly,
at least try to get it to work almost-correctly (e.g. add/subtract one
day using the DateDiff function) and then use VBS to finish the job.|||On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
Hi ukwebguy,
>
Hi ukwebguy,
>
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
>
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
>
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
>
>
--
Hugo Kornelis, SQL Server MVP
I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.
I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.
I'm using the following SQL..
SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
in MS SQL Server Express 2005
I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?
Regards
ukwebguynoone wrote:
Quote:
Originally Posted by
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
If for some reason you can't get the back-end logic to work correctly,
at least try to get it to work almost-correctly (e.g. add/subtract one
day using the DateDiff function) and then use VBS to finish the job.|||On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
Quote:
Originally Posted by
>Hi,
>
>I am designing an application which displays news topics until midnight
>on the DisplayUntil date and then they should drop out. Unfortunately,
>they seem to be dropping out at mid-day.
>
>I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
>fields so the date is in the format "25/09/2006 00:0:00" and I'm
>comparing them with GetDate() which (I suspect) includes the actual time
>as well.
>
>I'm using the following SQL..
>
>SELECT *
>FROM t_news
>WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
>
>in MS SQL Server Express 2005
>
>I think this is probably all too simplistic - is there a way to compare
>these dates so that they display from the beginning of the start date
>(ie 00:00:01) until the end of the last day (ie 23:59:59)?
>
>I'm getting so desparate that I'm even considering pulling the whole
>recordset and doing the comparison in VBScript - and that's desparate!
>Can anyone help me, please?
>
>Regards
>ukwebguy
Hi ukwebguy,
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
--
Hugo Kornelis, SQL Server MVP|||http://sql-server-performance.com/fk_datetime.asp
Madhivanan
Hugo Kornelis wrote:
Quote:
Originally Posted by
On Mon, 25 Sep 2006 17:49:33 +0100, noone wrote:
>
Quote:
Originally Posted by
Hi,
I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.
I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.
I'm using the following SQL..
SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
in MS SQL Server Express 2005
I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?
Regards
ukwebguy
>
Hi ukwebguy,
>
First, read the information in Tibor's "ultimate guide to the datetime
datatype": http://www.karaszi.com/SQLServer/info_datetime.asp.
>
If the DisplayFrom and DisplayUntil columns contain a date only (ie, the
time part is set to the default value of midnight), you could use this:
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom <= @.Today
>
If DisplayFrom and DisplayUntil contain time portions as well, then you
need to clarify your requirements. Here's a syntax that MIGHT work for
you, but only if my guesswork about your actual requirements is correct.
>
DECLARE @.Today smalldatetime
-- Strip time portion from current date and time
SET @.Today = DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
SELECT *
FROM news
WHERE DisplayUntil >= @.Today
AND DisplayFrom < @.Today + 1
>
>
--
Hugo Kornelis, SQL Server MVP
Date comparison problem
Hi,
I am designing an application which displays news topics until midnight
on the DisplayUntil date and then they should drop out. Unfortunately,
they seem to be dropping out at mid-day.
I'm storing the 'DisplayFrom' and 'DisplayUntil' dates as SmallDateTime
fields so the date is in the format "25/09/2006 00:0:00" and I'm
comparing them with GetDate() which (I suspect) includes the actual time
as well.
I'm using the following SQL..
SELECT *
FROM t_news
WHERE n_DisplayUntil >= GetDate() AND n_DisplayFrom <= GetDate()
in MS SQL Server Express 2005
I think this is probably all too simplistic - is there a way to compare
these dates so that they display from the beginning of the start date
(ie 00:00:01) until the end of the last day (ie 23:59:59)?
I'm getting so desparate that I'm even considering pulling the whole
recordset and doing the comparison in VBScript - and that's desparate!
Can anyone help me, please?
Regards
ukwebguyHi,
what about something like this here:
DECLARE @.Start DATETIME
DECLARE @.End DATETIME
SET @.Start = '20060924'
SET @.End = '20060924'
Select 1
Where @.Start <= GETDATE() AND DATEDIFF(dd,GETDATE(),@.END) >= 0
HTH, jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--
Tuesday, February 14, 2012
DATE AND HOUR
Hello people,
I'm migrating an application from asp to asp.net and access to ms sql 2005. On the old db, there's a table with a column to store date and a column to store time. Ms sql only works with date and time together, right? I created a query to get the time from time column and update the date column inserting the time. I did something like this:
UPDATE S_ACC_MONEY
SET Date_Oper = CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108)
Does anyone knows a better way to do that? Any other comments?
Thanks!
you might also want to do a final conversion to datetime as in:
Date_Oper =CONVERT(datetime,CONVERT(varchar, Date_Oper, 101) + ' ' + CONVERT(varchar, Time_Oper, 108))
Subscribe to:
Posts (Atom)