Re: Inserting regional settings specific data into sql server

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

"Niketa Mahana" <NiketaMahana@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht
news:D2A12E19-BFD8-41B2-A443-40711754EEA4@xxxxxxxxxxxxxxxx
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.

"Cor Ligthert [MVP]" wrote:

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

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





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.

"Cor Ligthert [MVP]" wrote:

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









.

Relevant Pages

  • Re: Inserting regional settings specific data into sql server
    ... Italianyou will observe that the settings used are 10/02/2006 ... it a better idea to Format it using the string format functions or a very ... into the sql database.Sorry to bug the shorts off you but u now have context ... By using the parameters, while the program is in the right culture setting, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to insert c# DateTime?
    ... > consider that the format in the SQL statement is NOT culture ... Even if the database program shows the data in a ... > using SQL. ...
    (microsoft.public.dotnet.framework.adonet)
  • The conversion of a char data type to a datetime data type resulted in an out-of-range datetime valu
    ... getting this error - i think its because im trying to put english date ... format into american date format ... im assuming that its going out of range as sql needs 02/13/2004 ... heh im guessing ive been testing code the past 2 weeks -- and now its the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Simple String To Date Time
    ... But there's more to the invariant culture ... with the English language but no specific country/region, ... be a neutral English culture. ... >> 1) Do you believe that the format the OP gave is culture-sensitive? ...
    (microsoft.public.dotnet.general)
  • Re: Can I specify location of noise file?
    ... About Language Resources ... As for integrating the fictional "language" into SQL Server FTS in a real ... production environment that uses "English" as the actual text language, ... >> therefore different noise word files for each table. ...
    (microsoft.public.sqlserver.fulltext)