0

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.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Leah Stortvedt
  • 1
  • 1
  • which database system are you using, looks like bigquery? please read up on https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms – nbk Aug 15 '23 at 00:52

1 Answers1

0

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.

Mark Prins
  • 1
  • 2