date_trunc alternative of BigQuery

date_trunc.sql

select timestamp(regexp_replace(string(ts), r'^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}).*', '\\1')) as date_trunc_second,
       timestamp(regexp_replace(string(ts), r'^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:).*', '\\100')) as date_trunc_minute, 
       timestamp(regexp_replace(string(ts), r'^(\d{4}-\d{2}-\d{2} \d{2}:).*', '\\100:00')) as date_trunc_hour,
       timestamp(regexp_replace(string(ts), r'^(\d{4}-\d{2}-\d{2}).*', '\\1 00:00:00')) as date_trunc_day,
       timestamp(regexp_replace(string(ts), r'^(\d{4}-\d{2}).*', '\\1-01 00:00')) as date_trunc_month,
       timestamp(regexp_replace(string(ts), r'^(\d{4}).*', '\\1-01-01 00:00')) as date_trunc_year,
       timestamp(regexp_replace(string(ts), r'^(\d{3}).*', '\\10-01-01 00:00')) as date_trunc_decade,
       timestamp(regexp_replace(string(ts), r'^(\d{2}).*', '\\100-01-01 00:00')) as date_trunc_century, 
       timestamp(regexp_replace(string(ts), r'^(\d{1}).*', '\\1000-01-01 00:00')) as date_trunc_millennium 
  from (select cast('2014-08-19 12:41:35.220000' as timestamp) as ts) as t

result.md

Row	date_trunc_second	date_trunc_minute	date_trunc_hour	date_trunc_day	date_trunc_month	date_trunc_year	date_trunc_decade	date_trunc_century	date_trunc_millennium
1	2014-08-19 12:41:35 UTC	2014-08-19 12:41:00 UTC	2014-08-19 12:00:00 UTC	2014-08-19 00:00:00 UTC	2014-08-01 00:00:00 UTC	2014-01-01 00:00:00 UTC	2010-01-01 00:00:00 UTC	2000-01-01 00:00:00 UTC	2000-01-01 00:00:00 UTC