Query to Reformat Odd Date-Time Values

[PCHomepage]

I am good at relational databases and SQL but haven't been able to figure out this one! I can do it in PHP but, as it's a one-time thing, it would be better to just run the query once and get it over with.

In the VARCHAR field is oddly formatted date-time data as: Jul 11 08:41:22. The month is always shown in 3-character format and the year is all 2008. Any idea how to strip the time completely and format the date as 2008-7-11?

Much simpler but for another VARCHAR column with the same data I want to leave only the time while stripping out the date.

 

[r937]

i don't think splitting date and time into separate columns is a good idea

but you can use STR_TO_DATE for the date, and a straight SUBSTRING for the time

both functions are well explained in da manual



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Thanks, I don't like the idea either but it is a table design that I am stuck with for now.

SUBSTRING() worked just fine on the ActivityTime field:

UPDATE table SET ActivityTime = SUBSTRING(ActivityTime,8,11)

However, I am still having trouble with the non-standard date format and am getting errors:

SELECT STR_TO_DATE(SUBSTRING(ActivityDate,1,6),%M %e) FROM table

Maybe I have used the incorrect syntax but STR_TO_DATE does not seem to have anything for a three character month name.

 

[PCHomepage]

Oops! I see my mistake: I left off the quotes and it needed a TRIM() as the day was both single and 2-digit values. It should be:

UPDATE table SET ActivityDate =  STR_TO_DATE(TRIM(SUBSTRING(ActivityDate,1,6)),'%b %e')

Thanks for pointing me in the proper direction!

 

[r937]

excellent

i thought it would've needed concatenating '2008' onto it before using STR_TO_DATE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PCHomepage]

Yes, I did that too but remembered it after I posted since I hadn't yet run the update query.

In case it helps anyone else (or myself later on), it's:

UPDATE table SET ActivityDate =  STR_TO_DATE(CONCAT('2008 ', TRIM(SUBSTRING(ActivityDate,1,6))),'%Y %b %e')

This takes a partial date formatted as Jul 4 and reformats it properly as 2008-07-04 and works as long as the year is the same.

Sorry, I didn't test the above as I ran it in smaller bits. This should work, though.

Thanks again!