Date Format in Csv File - US and UK Format

Hi

I have a file csv file, sometimes the date is in UK and sometimes it is US Format.
The file is loaded onto SQL Server using DTS, how do you detect if the date is US or UK so to convert it in the correct format.
I know that the below converts from US to UK, but I need something to detect it row by row and then convert it accordinarly
CONVERT(datetime, MIFL.[Report Date], 101) as 'RepDate',

Any help will be great.
ITHELPMEAsked:
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.

allieCommented:
As far as I know, if you're working with a .csv file, and the date format can be different from one row to the next, there is no way to automatically determine if it's US or UK.  There's just no way to look at the text like '03/04/2009' and know if it's March 4th or April 3rd.

Is it possible to get the date in MMM-dd-YYYY format, like 'APR-03-2009', which could be standardized?
0
Anthony PerkinsCommented:
>>The file is loaded onto SQL Server using DTS, how do you detect if the date is US or UK so to convert it in the correct format.<<
You can't.  The best you can do is load the data in a staging table (I trust you are already doing that) as character data and check then.
0
ITHELPMEAuthor Commented:
I am loading it into a staging table but how do I check it and convert it from here
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Anthony PerkinsCommented:
Are all the dates in one format or the other?  Or could each row be different?
0
nmcdermaidCommented:
Are yuo looking at the CSV in Excel or notepad? Excel applies its own formatting and that clouds the issue. Look at it in notepad and see if it really does change formats. Unless your source system specifically stores them in this ambiguous format then I would be suprised if the format does change within the file.
Basically there is no way to tell if 01-02-09 is 1st Feb 09 or 2nd Jan 09 unless you have something telling you what format it is.
Why don't you post some example data.
0

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
ITHELPMEAuthor Commented:
When I created the temp table, I created it as DateTime, I changed this to Varchar(50) and import the data from the file, then I do a convert when extracting the data accross.
Thanks for all your help!
0
Anthony PerkinsCommented:
You are welcome.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.