asked on

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?

RameshS

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 asp.net 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.
https://www.experts-exchange.com/questions/25782987/date-time-format-on-localhost-and-iis-server.html?cid=1131&anchorAnswerId=30198681#a30198681

     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

mystasm

ASKER

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

ASKER CERTIFIED SOLUTION

RameshS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mystasm

ASKER

So is there Microsoft documentation