Re: Inserting regional settings specific data into sql server

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

"Cor Ligthert [MVP]" wrote:

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
    ... All EU countries withouth the two English speaking use the format dd-MM-yy ... 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 ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Data Format on SharePoint pages - how to set
    ... I'm not interested in changing how dates are displayed in custom lists, but rather the default date format for the all pages that are automatically created by SharePoint - e.g. the Shared Documents page. ... The user might choose to override some of the values associated with the current culture of Windows through Regional and Language Options (or Regional Options or Regional Settings) in Control Panel. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Data Format on SharePoint pages - how to set
    ... \par global change for the entire SharePoint installation. ... \par date format for the all pages that are automatically created by SharePoint - ... \par says that the .NET Culture can be set for a web site in the web.config file. ... \par Regional Options or Regional Settings) in Control Panel. ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: How to Set Datetime on SQL 2000 server
    ... I ran Query Analyzer on the SQL Enterprise Manager and ran: ... Windows 2000 server and the test 2003 server actually use US date format, ... regional settings, since you now are dependent on that as well. ...
    (microsoft.public.sqlserver.setup)
  • Re: Passing DATETIME to an SQL stored procedure
    ... The easiest way is to format the date as a string and insert that into the ... to be in sync with the settings that SQL Server is using. ...
    (microsoft.public.vc.mfc)