Regional Settings DateTime Representation

Suppose you have implemented a multi tiered application. On the data tier (which is physically on a different machine than the application tier) we have MS SQL. Now SQL stores its dates internally however it sees fit.

From the application tier, we run different queries against the data tier. Say we run a sql query to get a specific date. Even if we used Typed Data Sets, how is the .net runtime able to determine the schema of the field within the dataset (ie. if we have different regional settings on each of the Tiers: dd/mm/yyyy vs mm/dd/yyyy).

Is there some sort of metadata in the serialized dataset that describes the schema to prevent data integrity problems?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If your client application is running in PC having different regional settings, you can override the regional settings by setting a default culture settings to your application. For example, the following code in Windows Form's Load event will change the culture settings to british english format. It will accept/process  the date time format in dd/mm/yyyy format.

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB");

The culture settings can be changed in web.config for application.

Alternatively you can pass the date values in the format acceptable by you SQL Server instance from you application. For example, if your SQL database accepts date in dd/mm/yyyy format.

Also refer this link.
     CultureInfo cultInfo = new CultureInfo("en-GB", true); 
     DateTimeFormatInfo formatInfo = cultInfo.DateTimeFormat; 
     formatInfo.ShortDatePattern = "dd/MM/yyyy"; 
     formatInfo.LongDatePattern = "dd MMMM yyyy"; 
     formatInfo.FullDateTimePattern = "dd MMMM yyyy HH:mm:ss"; 
     DateTime date1 = DateTime.Parse("30.01.2010", formatInfo);

Open in new window

mystasmAuthor Commented:
Yes I am aware that you can programmatically change the way a date is formated. I can also use the built in date to string functions.

My question is more fundamental than that. Given a dataset how do you determine what the schema of a date field is.

Hence my question about the serialized dataset object (I am not sure that the dataset itself will have metadata containing this info).

For example in the following code, how do we know what format the sql.ExecuteScalar() is going to provide without knowing the regional settings of the Sql Server.

Or do we know for every MS SQL server out there that the date is going to be presented as yyyy-MM-dd hh:mm:ss when a select query is performed on a date field?
SqlConnection sqlCon = new SqlConnection(conStringToSomeRemoteServerRegionalSettingsUnknown);

SqlCommand cmd = new SqlCommand("SELECT Distinct created_date from SomeTable where someCondition", sqlCon);

DateTime dt = DateTime.Parse(sql.ExecuteScalar().ToString());

Open in new window

It is automatically handled by ADO.NET. Assume that the regional settings of your PC supports mm/dd/yyyy format and the sql server has dd/mm/yyyy format. When you execute the above ExecuteScalar() or any other ADONET  method, the typed dataset/loca variable will have the date in your regional settings format and not the SQL server format.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mystasmAuthor Commented:
So is there Microsoft documentation
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.