John Spencer John Spencer - 4 months ago 4
SQL Question

MYSQL Date select between

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.