date/time in different formats

[Darin]

I am having major issues w/ date and time between SQL and the PC.

The SQL database has EVERYTHING as mm/dd/yyyy hh:mm:ss (standard english
datetime).
But, on english PC's it is fine. My problem (right now) is mexico PC's
setup for spanish where the date is dd/mm/yyyy. It seems sometimes when
I read the database the date is autoformatted into the dd/mm/yyyy and
other times it isn't, and I haven't figured out a reason why.

Does anyone have any suggestions as to how to bullet proof globalization
between an always US SQL database and different PC's?

THanks.

Darin

 

[Cor Ligthert [MVP]]

Darin,

SQL server *has not* everything in mm/dd/yy hh:mm:ss (which is by the way
not the standard English datetime, but the USA culture DateTime
representation, the standard English DateTime is the same as in Spain with
the difference that a 12 hour clock with pm/am is used)

A DateTime in SQL server is in ticks from 1000/3 milliseconds starting at
the date that the British Empire including their colonies in America started
with the Gregorian calendar. As forever in standardisation cases Britain
was with that the last in the so called western world (although the later is
now taken over by the USA), so that start date works almost in every country
to calculate accurate calendar dates where the Gregorian calendar is used.

Your representation of the datetime will be given depending on the Culture
settings of your computer.

(Be aware that the VB.Net debugger (not the C# one which shows them correct)
can only shows dates in literal format which is in USA notation).

I hope this helps,

Cor

 

[Claes Bergefall]

What datatype are you using for the column in the SQL database? How are you
reading the data in your VB code?

The bullet proof way to support globalization is to use real date data types
(and ISO format if you need to work with strings for some reason)

/claes