asked on

How to detect date format

I have an application that can accept both US and Europe date format but my MS SQL 2008 can only accept 1 format and we prefer US. My plan is to check first if the date is in Europe format then convert it before saving.

Would it be proper to do the checking and conversion using SP or just to in VB.net code before saving?
It would be best if the recommendation comes with the sample code.

Thanks

Sharath S

What is the data type of your column? If it is datetime, you need not to worry about the format as long as it is valid date. You need to format the date into your required format when you display it.
Let me know if your column datatype is not datetime but varchar/nvarchar.

SOLUTION

tigin44

membership

This solution is only available to members.

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

Start Free Trial

JaimeJegonia

ASKER

Sharath_123
My Data type is DateTime but it does not accept: 22/02/2011

tigin44:
The format we prefer is this:
05/15/2010 20:15:00

Thanks for the prompt replies.

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

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

Start Free Trial

JaimeJegonia

ASKER

carl_tawn:

Is that possible with SP?

Thanks.

Carl Tawn

It's easier if you can pass it to the SP in that format already. You can do date conversion in T-SQL, but you would need to know the input format in order to know how the date will be interpreted.

For example, if you passed "01/04/2010" to an SP, the SQL Server has no idea if you intended that to be "1st April 2010" or "4th January 2010", and it will interpret it based on its collation settings, which may or may not give the result you want.

If your app allows multiple date cultures then it is always easiest to handle the conversion in your application and send them to SQL Server in one standard format.

SOLUTION

omegaomega

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

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

Start Free Trial

JaimeJegonia

ASKER

Carl_Tawn:

If my Date I am receiving is 22/02/2011 00:20 which is already a string, how can I use your recommended formatting?

string utcDate = yourDate.ToString("yyyy-MM-dd HH:mm:ss");

I can neither convert 22/02/2011 00:20 to the current Date Culture.

Any Idea?

Thanks

JaimeJegonia

ASKER

No comments