Solved

How to detect date format

Posted on 2011-02-22
10
547 Views
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 VB.net code before saving?
It would be best if the recommendation comes with the sample code.

Thanks
0
Comment
Question by:JaimeJegonia
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 40

Expert Comment

by:Sharath
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.
0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 100 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), ,,,,)

0
 

Author Comment

by:JaimeJegonia
ID: 34958780
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.
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 100 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

0
 

Author Comment

by:JaimeJegonia
ID: 34959205
carl_tawn:

Is that possible with SP?


Thanks.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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.
0
 
LVL 12

Assisted Solution

by:omegaomega
omegaomega earned 100 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:

    My.Application.Culture.DateTimeFormat.ShortDatePattern

Cheers,
Randy
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 100 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), ,,,,)
0
 

Author Comment

by:JaimeJegonia
ID: 34983513

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

0
 

Author Closing Comment

by:JaimeJegonia
ID: 38081850
No comments
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now