Re: Inserting regional settings specific data into sql server

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







.