Inserting Regional setting specific data into sql server

Hi,
We are in the process of making product in windows forms that is localized
presently for all european countries.We are supposed to support all eastern
and western european date , time and number formats.However we are currently
facing a problem that say a user in Italy uses our product and user's
settings have a "." as a time seperator sql server is unable to insert such
time values into the database.We get an error for date-time format is not
recognizable by sql server.This also gives us a problem if the date and time
seperator are both "." .Since several countries in europe do support such
settings we are in a fix.The minute any C# code at the GUI level encounters
a
"." date/time seperator the Convert.ToDateTime functions fail and we are
unable to go ahead.
While doing some research for this problem we came up with a solution that
using CultureInfo.InvariantCulture to change all our date-time values before
we pass them onto Convert.ToDateTime functions . We have found this works
however what we dont understand is that we use the 120 datetime format while
inserting data into sql server, this format expects yyyy-mm-dd hh:mm:ss,
which is not what CultureInfo.InvariantCulture expects.But the queries work
perfectly inspite of that.What we are looking for is an explanation for this
since we cannot afford to release yet more patches and releases for our
product once we fix this problem we want the solution to be permanent.
Please guide us as to if the above is a solution to our problem and what is
the explanation for it, or what can be a solution to our problem.
With deepest thanks,
Niketa Mahana
Associate Consultant
Siemens Information Systems Ltd.Niketa
SQL Server 'loves' YYYYMMDD format . However , to display date on the
client site it's a matter of his/her regional settings and it has nothing to
do with SQL Server.
Use any FORMAT functions to dispaly dates on the client
"Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
news:839F5930-1361-49BC-9E46-87B1AF63EEDD@.microsoft.com...
> Hi,
> We are in the process of making product in windows forms that is localized
> presently for all european countries.We are supposed to support all
> eastern
> and western european date , time and number formats.However we are
> currently
> facing a problem that say a user in Italy uses our product and user's
> settings have a "." as a time seperator sql server is unable to insert
> such
> time values into the database.We get an error for date-time format is not
> recognizable by sql server.This also gives us a problem if the date and
> time
> seperator are both "." .Since several countries in europe do support such
> settings we are in a fix.The minute any C# code at the GUI level
> encounters a
> "." date/time seperator the Convert.ToDateTime functions fail and we are
> unable to go ahead.
> While doing some research for this problem we came up with a solution that
> using CultureInfo.InvariantCulture to change all our date-time values
> before
> we pass them onto Convert.ToDateTime functions . We have found this works
> however what we dont understand is that we use the 120 datetime format
> while
> inserting data into sql server, this format expects yyyy-mm-dd hh:mm:ss,
> which is not what CultureInfo.InvariantCulture expects.But the queries
> work
> perfectly inspite of that.What we are looking for is an explanation for
> this
> since we cannot afford to release yet more patches and releases for our
> product once we fix this problem we want the solution to be permanent.
> Please guide us as to if the above is a solution to our problem and what
> is
> the explanation for it, or what can be a solution to our problem.
> With deepest thanks,
> Niketa Mahana
> Associate Consultant
> Siemens Information Systems Ltd.|||Hi Uri
Thanks for that, we are aware sql prefers this YYYYMMDD format however if u
take a datetime that is like 12/22/2005 06.10.12 , and try to insert this as
is using 120 sql format sql server does not accept it, however if u make thi
s
into a string and then format it using cultureInfo.InvariantCulture then it
makes the string 22/12/2005 06:10:12 -> note the time seperator has become a
: now instead of dot and now this date can be inserted into sql server using
120 format.However what we dont understand is why does 120 format accept
22/12/2005 06:10:12 when actually it is supposed to expect YYYYMMDD?Any clue
s
on that will be MOSSTT helpful
"Uri Dimant" wrote:

> Niketa
> SQL Server 'loves' YYYYMMDD format . However , to display date on the
> client site it's a matter of his/her regional settings and it has nothing
to
> do with SQL Server.
> Use any FORMAT functions to dispaly dates on the client
>
> "Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
> news:839F5930-1361-49BC-9E46-87B1AF63EEDD@.microsoft.com...
>
>|||Nikete

> take a datetime that is like 12/22/2005 06.10.12 , and try to insert this
> as
> is using 120 sql format sql server does not accept it
Run it on QA
select convert(varchar(20),getdate(),120)
declare @.dt varchar(20)
set @.dt='12/22/2005 06.10.12'
select convert(datetime,
substring(@.dt,1,10)+substring(replace(@.d
t,'.',':'),11,len(@.dt)),120)
What did it return?
In case of datetime why not just using
YYYYMMDDTHH:MM:SS
"Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
news:9DBC8BB7-0F9E-4E9E-8CEE-2D3A38311499@.microsoft.com...
> Hi Uri
> Thanks for that, we are aware sql prefers this YYYYMMDD format however if
> u
> take a datetime that is like 12/22/2005 06.10.12 , and try to insert this
> as
> is using 120 sql format sql server does not accept it, however if u make
> this
> into a string and then format it using cultureInfo.InvariantCulture then
> it
> makes the string 22/12/2005 06:10:12 -> note the time seperator has become
> a
> : now instead of dot and now this date can be inserted into sql server
> using
> 120 format.However what we dont understand is why does 120 format accept
> 22/12/2005 06:10:12 when actually it is supposed to expect YYYYMMDD?Any
> clues
> on that will be MOSSTT helpful
> "Uri Dimant" wrote:
>|||Hi Uri,
Now your getting to our problem, see if i know the seperator is going to be
a "." then i can put in a replace function however time seperators are user
editable, if u open control panel and in the regional settings for a country
custimse a time seperator and put in say # then the replace functions dont
worl becuase the replace is looking for a . not #, and in that case naturall
y
sql crashes. So to support all seperators before we dynamically concat a
string and make it into a sql query we thought we would take all date times
in that query make it into a string and then format it using
CultureInfo.Invariant culture, i wanted to confirm if microsoft thinks this
is a good approach to insert datetimes that are regional setting
specific.Please see my eg below-
User enters -> 12;12;2005 06;06;09 (; is the time/date seperator)
in the DB layer of the application we should
string w = "12/12/2005 06;06;09 ";
w= w.ToString(CultureInfo.InvariantCulture); // this makes w look like
12/12/2005 06:06:09 -> note it has made the time seperator : and the date
seperator /
now we would do insert into myTable(dtcol1) values(convert(datetime,w,120))
, but is this safe and will this always work ?
"Uri Dimant" wrote:

> Nikete
>
> Run it on QA
> select convert(varchar(20),getdate(),120)
> declare @.dt varchar(20)
> set @.dt='12/22/2005 06.10.12'
> select convert(datetime,
> substring(@.dt,1,10)+substring(replace(@.d
t,'.',':'),11,len(@.dt)),120)
> What did it return?
> In case of datetime why not just using
> YYYYMMDDTHH:MM:SS
>
> "Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
> news:9DBC8BB7-0F9E-4E9E-8CEE-2D3A38311499@.microsoft.com...
>
>|||Niketa
Well, in that case why you allow to user inserting a date and time
As I know there is a calendar control to do that.
Always that will work
YYYYDDMMTHH:MM:SS
"Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
news:B7DDBD90-D733-43B3-B557-4D971C4F7CE7@.microsoft.com...
> Hi Uri,
> Now your getting to our problem, see if i know the seperator is going to
> be
> a "." then i can put in a replace function however time seperators are
> user
> editable, if u open control panel and in the regional settings for a
> country
> custimse a time seperator and put in say # then the replace functions dont
> worl becuase the replace is looking for a . not #, and in that case
> naturally
> sql crashes. So to support all seperators before we dynamically concat a
> string and make it into a sql query we thought we would take all date
> times
> in that query make it into a string and then format it using
> CultureInfo.Invariant culture, i wanted to confirm if microsoft thinks
> this
> is a good approach to insert datetimes that are regional setting
> specific.Please see my eg below-
> User enters -> 12;12;2005 06;06;09 (; is the time/date seperator)
> in the DB layer of the application we should
> string w = "12/12/2005 06;06;09 ";
> w= w.ToString(CultureInfo.InvariantCulture); // this makes w look like
> 12/12/2005 06:06:09 -> note it has made the time seperator : and the date
> seperator /
> now we would do insert into myTable(dtcol1)
> values(convert(datetime,w,120))
> , but is this safe and will this always work ?
> "Uri Dimant" wrote:
>|||Why would you let the user enter datetime values at all? Wouldn't it be
easier (for you and your users) to use a date picker component?
ML
http://milambda.blogspot.com/|||Hi Uri,
Its this way, we create a file on our file system and we need to insert the
file modification date into the database.The file modification date comes as
per current regional settings , because we create an instance of this file
and then pick up its modification date..now if the current regional settings
are having some funny seperators then while inserting sql fails..so no use o
f
calendar control..hence i asked about cultureInfo.InvariantCulture.Any input
s?
"Uri Dimant" wrote:

> Niketa
> Well, in that case why you allow to user inserting a date and time
> As I know there is a calendar control to do that.
> Always that will work
> YYYYDDMMTHH:MM:SS
>
> "Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
> news:B7DDBD90-D733-43B3-B557-4D971C4F7CE7@.microsoft.com...
>
>|||Niketa
> calendar control..hence i asked about cultureInfo.InvariantCulture.Any
> inputs?
How does it relate to SQL Server?
I'm not an expert of C#/VB.NET , sorry.
"Niketa Mahana" <NiketaMahana@.discussions.microsoft.com> wrote in message
news:732D6AD5-AB34-4486-99B1-3862D962176F@.microsoft.com...
> Hi Uri,
> Its this way, we create a file on our file system and we need to insert
> the
> file modification date into the database.The file modification date comes
> as
> per current regional settings , because we create an instance of this file
> and then pick up its modification date..now if the current regional
> settings
> are having some funny seperators then while inserting sql fails..so no use
> of
> calendar control..hence i asked about cultureInfo.InvariantCulture.Any
> inputs?
> "Uri Dimant" wrote:
>