Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to detect date format

Posted on 2011-02-22
Medium Priority
Last Modified: 2012-06-27
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 code before saving?
It would be best if the recommendation comes with the sample code.

Question by:JaimeJegonia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
LVL 41

Expert Comment

ID: 34958546
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.
LVL 26

Assisted Solution

tigin44 earned 200 total points
ID: 34958580
you can convert it to the format as you wanted while storing that data i.e.  
I assume that your data type is datetime...

INSERT INTO table....
VALUES(...., CONVERT(datetime, someDateTimeValue, 102), ,,,,)


Author Comment

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

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

Thanks for the prompt replies.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

LVL 52

Accepted Solution

Carl Tawn earned 200 total points
ID: 34958980
You will make things a lot easier for yourself if you send the dates to your SQL server in UTC format. Just because you send dates to the QSL Server in US format that doesn't mean that the SQL Server will necessarily interpret it as US format as it will depend on the  SQL Servers collation settings.

UTC is a standard format which will remove the ambiguity:
string utcDate = yourDate.ToString("yyyy-MM-dd HH:mm:ss");

Open in new window


Author Comment

ID: 34959205

Is that possible with SP?

LVL 52

Expert Comment

by:Carl Tawn
ID: 34959303
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.
LVL 12

Assisted Solution

omegaomega earned 200 total points
ID: 34963728
Hellol, JaimeJegonia,

I'm in agreement with carl_tawn's suggestion.  And if it's of any value in making the conversion, you can get the current user's date format from:


LVL 41

Assisted Solution

Sharath earned 200 total points
ID: 34964107
Do you have time part associated with your date column? If not, can you try like this.
INSERT INTO table....
VALUES(...., CONVERT(datetime, someDateTimeValue, 103), ,,,,)

Author Comment

ID: 34983513


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?



Author Closing Comment

ID: 38081850
No comments

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question