I am using an API to collect some data from my client. however the date's of orders come in this format 06/09/2016 07:30PM UTC
at the moment I am just putting the data into my MYSQL table and storing it as Varchar,all fine for now but I will want to run some queries using select between (the range of dates)
I have tried changing the table structure so the column is Date instead of varchar but the dates come in as all 00000000's
I can return records using wild cards but not in a between range of dates.
Im thinking of trimming the data I don't need from the end then putting into a new table
Any suggestions on the best way to proceed would be appreciated.
select str_to_date(date_column, '%d/%m/%Y %h:%i%p') from your_table
to convert it to date: Example.
You could add another column of type
datetime and put the values there.
update your_table set new_date_column = str_to_date(old_date_column, '%d/%m/%Y %h:%i%p')
Then drop the old column and rename the new one.
see here for how to format date strings.