Need to convert column to datetime

Question

I'm trying to convert an entire column to datetime in BigQuery. I had to bring it into SQL as string because datetime kept throwing errors. The current format of my data is

Row date_hour
1   4/12/2016 3:00:00 AM
2   4/12/2016 4:00:00 AM
3   4/12/2016 5:00:00 AM
4   4/12/2016 6:00:00 AM

I want to convert this to datetime, so I can summarize by days of the week, but all my syntax keeps throwing errors.

I tried:

SELECT date_hour
FROM `turing-bebop-390023.Fitness_tracker_data.hourly_steps` 
CONVERT(datetime, date_hour, 100)
LIMIT 1000

I admit that I'm very new to SQL, so I don't know exactly how to write the syntax.

Answer 1

The last parameter passed to convert defines how the date string is parsed. The 100 you have is expecting something like mon dd yyyy hh:miAM (or PM). I'd recommend using 101 (for US date formats, that is. For British / European etc, 103 will get the day and month the right way around).

You could try this:

SELECT CONVERT(datetime, date_hour, 101) FROM turing-bebop-390023.Fitness_tracker_data.hourly_steps

and see if that works.