How to import inconsistent dates into MySQL

I have a text file where a field is date/time; however, the date component is not exactly MM/DD/YYYY e.g. 7/5/2013 12:30:01 AM instead of 07/05/2013...

In the load statement, I first tried:

...
Set OffMarketDate= str_to_date(@OffMarketDate,'%m/%d/%Y');

Open in new window


But this gave Error 1292 Truncated incorrect date value: 7/5/2013 12:30:01 AM

Odd to me that this seems to work fine:
select str_to_date("7/5/2013 12:30:01 AM",'%m/%d/%Y');

Open in new window


Anyway, so I wanted to try something like
select left(str_to_date("7/5/2013 12:30:01 AM",'%m/%d/%Y'),10)

Open in new window


but the date will be somewhere between 8 and 10 characters, so hardcoding 10 is a problem.

Would appreciate some help resolving this

With Regards-
Sam
SAbboushiAsked:
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can use LOCATE() function to get the value dynamically:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_locate
select left(str_to_date('7/5/2013 12:30:01 AM','%m/%d/%Y'), LOCATE( ' ', '7/5/2013 12:30:01 AM' )) 

Open in new window

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
SAbboushiAuthor Commented:
Thanks-

Your select statement returned '2013-07-0'...?

And so how would I use this in my load statement?
0
SAbboushiAuthor Commented:
>> Your select statement returned '2013-07-0'...?
OK - it looks like locate is zero-based, so I need to add 1:
LOCATE( ' ', '7/5/2013 12:30:01 AM' )+1

Open in new window


>> so how would I use this in my load statement?
Thanks for pointing me in the right direction; I think I will use:
select str_to_date(left('7/5/2013 12:30:01 AM', LOCATE( ' ', '7/5/2013 12:30:01 AM' )+1),'%m/%d/%Y') ;

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I did the wrong way round...
select str_to_date(left('7/5/2013 12:30:01 AM', LOCATE( ' ', '7/5/2013 12:30:01 AM' ), '%m/%d/%Y') 

Open in new window

0
SAbboushiAuthor Commented:
Oops - my bad too... seeing that I am... what's another word for... "anal"...?  ... it should be "-1" to get rid of the trailing space before conversion, although the str_to_date() doesn't seem to mind the trailing space.  

select left('8/31/2013 12:30:08 AM', LOCATE( ' ', '8/31/2013 12:30:08 AM' )) ;     -- '8/31/2013 '
select left('8/31/2013 12:30:08 AM', LOCATE( ' ', '8/31/2013 12:30:08 AM' )-1) ;  -- '8/31/2013'
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
MySQL Server

From novice to tech pro — start learning today.