Inserting regional settings specific data into sql server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

DateTime and Short Time are not notated in SQL server in any local variant.

It is notated in ticks starting at 1753 and 1900.

If you avoid giving DateTimes as strings to the SQL Sever, than you will not
have any problem.

Strings can be converted in your program by the commands.
CDate (Visual Basic)
DateTime.Parse
Convert.ToDateTime

To give the datetime information to the server you "should" use parameters.
See this as most simple sample.

http://www.vb-tips.com/default.aspx?ID=886bba68-8a2f-4b99-8f66-7139b8970071

By the way InvariantCulture is for inside the English Language culture. The
most people in that use September 11 as date while the most countries use 11
September as date.

I hope this helps,

Cor
 
Hi Cor,

Perhaps i am not clear in my problem , our application is culture specific
in Italian regional settings say i make the date and time seperator a "." ,
now all dates-times being displayed are with . which is fine , but when i go
ahead to save data in the date base i need to put these dates into strings
which concat into an sql query, at this point see the eg below
DateTime dtProblem = Convert.ToDateTime("02.02.2005 06.05.22")
now the value of dtProblem is coming as 6/5/2022 which is WRONG it should
have been 2/2/2005, now if i go ahead and do an insert like insert into
myTable(dtCol1) values(convert(datetime,'02.02.2005 06.05.22',120), this
Bombs on sql becuase sql does not understand the format.So to work around it
i came up with a solution

string strmyDate = "02.02.2005 06.05.22";
strmyDate = strmyDate.ToString(CultureInfo.InvariantCulture);
now the str my date is formatted correctly for sql
insert into myTable(dtCol1) values(convert(datetime,strmyDate,120)
This works perfectly for all date time seperators even if the user decides
to use # as time seperator, what i need to under stand is that sql datime
format 120 required yyyyMMdd and CultureInfo.InvariantCulture formats it as
ddMMyyyy and yet teh query works fine ....WHYYYYY.Please help me out.
 
Niketa,

It was clear,
Perhaps i am not clear in my problem , our application is culture specific
in Italian regional settings say i make the date and time seperator a "."
,
now all dates-times being displayed are with . which is fine , but when i
go
ahead to save data in the date base i need to put these dates into strings
Click to expand...

I have so often been in Italy that I could talk in that language (not
write), however I have been now not been a long there, but I still love
Italy, the Italian culture, Italians and Italianwoman. The culture setting
from Italy is now exactly the same as mine even including the Euro.

As I tried to explain, you never should supply dates to your database as a
string. You are as well not retrieving them as string.

The SQL/Access databases don't know anything about the decimal or whatever
separator or addition as the English pm/am.

You should use parameters to supply whatever value to your database. In
those parameters is automaticly set the right format confirming your culture
setting.

See my sample, this is as well beside dates for every value, however the
date gives mostly the most problems therefore is the sample with dates.

Cor
 
Hi Cor,
I love Italian food and the love for that too is dying after the problems we
are having with our application in Italy but be that as it may -> 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 using cultureInfo.InvariantCulture on
the modification date before inserting it.Any inputs?Ok can you confirm one
thing does sql server only accept /for date seperator and : for time
seperator or do other seperators work.
 
Niketa,

I am sorry I cannot make it clear to you. I will try to it a last time.
All EU countries withouth the two English speaking use the format dd-MM-yy
hh:mm:ss
All English speaking countries (exept the USA) use the format dd-MM-yy
hh:mm:ss t (t = pm/am)
The USA which has no official language uses MM-dd-yy hh:mm:ss t
The ISO format as by instance official used by China as the datetime has as
format yy-MM-dd hh:mm:ss

By using the parameters, while the program is in the right culture setting,
than all those formats results using SQLparameters in the way the SQL server
wants it. (Did you real look at the sample)

If somebody is using a format setting that is not equal to his own computer,
than you can use probably the best in advance the dateTime.parse and/or
dateTime.parseExact first in which you can tell what is the culture of that
datetime string.

If somebody is using completely its own format than he/she is a person that
should not be allowed to reach a keyboard.

I wrote already about the InvariantCulture from which you read in my opinion
something as the NeutralCulture. It is an English Culture setting based on
those slight differences in the English Culture. By instance will it accept
15 november and november 15 as it will be used in a long date patern.

http://msdn2.microsoft.com/en-us/library/4c5zdc6a.aspx

I hope this helps,

Cor
 
If somebody is using a format setting that is not equal to his own
computer, than you can use probably the best in advance the dateTime.parse
and/or dateTime.parseExact first in which you can tell what is the culture
of that datetime string.
Click to expand...
This as well as the date and time comes in a textfile or html file as string
in a home made format as in past where made (this cannot in another way in
a file except an XML file)

ddMMyyyy or ddMMyy or ddMMy or whatever.

You can than use the DateTime.ParseExact with Iformatprovider and the right
paterns

ParseExact overloaded version (string, string etc)
http://msdn.microsoft.com/library/d.../frlrfsystemdatetimeclassparseexacttopic3.asp


DateTimeFormatInfo
http://msdn.microsoft.com/library/d...lizationdatetimeformatinfoclasstopic.aspMaybe is that what you are looking for and are you receiving the date timesas documents.Cor
 
Hi Cor,
Firstly thank you ever so much for being so patient and so prompt.Bear with
me for a bit more , according to you "All EU countries withouth the two
English speaking use the format dd-MM-yy hh:mm:ss" , but i beg to differ this
is not the case take for eg Italy in regional settings if u checked out
Italian(Italy) you will observe that the settings used are 10/02/2006
14.55.16 , please try to insert this date in sql server it will BOMB.We have
confirmed this on Italian XP as well. I am extremely aware of how
datetime.Parse , convert.todatetime etc function since it has now been 4 +
years that i have been working with dotnet.Please understand that are
requirements are such that dates are coming in such formats because we read a
number of dates directly from the file system due to the nature of our
product (I work with Siemens Information System and we are into telematics /
GPS product developement) hence I was thinking that once get such a date is
it a better idea to Format it using the string format functions or a very
very simple solution maybe to do a use cultureinfo.invariantculture on the
date so that irrespective of format the date will get converted and inserted
into the sql database.Sorry to bug the shorts off you but u now have context
to my problem so your stuck with it ;)
Awaiting your response
 
Hey Cor,
Hmm this is fine, so we would detect in run time the current culture and
then insert the dates in the sql query.Ok let me check it out in code and i
will get back to you ..curse all multilingual applications to hell.
 
Niketa,

I am always in doubt about the seperators between times. So thank you for
telling me this about Italy.

However try it first with that Parameter sample that I showed you.

If you are using C# something as, not checked changed in this message..

cmd.Parameters.Clear();
SQLCommand cmd = new SQLCommand("SELECT * FROM MyTable WHERE MyDate BETWEEN
@BeginDate And @EndDate", Conn);
cmd.Parameters.Add("@BeginDate", DateTime.Parse(txtBeginDate.text));
cmd.Parameters.Add("@EndDate", DateTime.Parse(txtEndDate.text));

If it is a true regional setting, than it should work.

Otherwise you have to translate it using that DateTime.ParseExact from which
I gave you all the links in my other message. However than you have
localized your program.

Cor
 
Hi Cor,

We dont use stored procedures unfortunately (:() so i cant use the advantage
of a parameter array ..ours is a windows based application which some
miserable idiot designed (before i joined siemens) and everywhere inline
queries are directly fired from the C# code using ExecuteNonQuery etc which
is why this problem has come up becuase we are in the 4th pahase of the
product which is used by european control authorities and those dates are
particularly vital to the systems integrity checks but i am gonna try out
your parse exact suggestion using a number of different regional combinations
and then testing the app on some of the regional XPs specialy italian,
lithuanian and czech god those buggers are barking up our tree , please
though can you still give me some insite on cultureInfo.InvariantCulture
sorry to keep harping on that some how my dotnet gut says i would be safer
using that that relying on fframework 1.1's tardy handling of datetime
through both teh parse and convert fucntions.Incidently dont u ever sleep...u
always manage to reply so promptly.
 
Nikita,
We dont use stored procedures unfortunately (:()
Click to expand...

Parameters have nothing to do with stored procedures in Net.

so you can use the advantage
of a parameter array ..ours is a windows based application which some
miserable idiot designed (before i joined siemens) and everywhere inline
queries are directly fired from the C# code using ExecuteNonQuery etc
which
is why this problem has come up becuase we are in the 4th pahase of the
product which is used by european control authorities and those dates are
particularly vital to the systems integrity checks but i am gonna try out
your parse exact suggestion using a number of different regional
combinations
and then testing the app on some of the regional XPs specialy italian,
lithuanian and czech god those buggers are barking up our tree , please
though can you still give me some insite on cultureInfo.InvariantCulture
Click to expand...

I have sent a link about that, which clears it in my idea very much
 
Hi
My name is Kwame. I am studying telematics in NFE level 2 and fancies to
have a couple of ideas from experience guys as I could see from this site.
 
Back
Top