CODEWITHSUNDEEP
sqlgoogle-bigquery
Ahmed
Ahmed

Reputation: 37

Adding colon after two numbers using Standard SQL

I'm trying to run a query in Big-Query using standard SQL.

I have a column in my table where time is presented in this format: 170519

What I want to do is to add colon (:) after every two numbers to get results like this: 17:05:19

How do i do this?

I have been searching but have not been able to find a solution to this. Maybe I'm not searching with the correct words?

Thanks for the help!

EDIT:

So @Gordon Linoff 's answer works for one column.

Now how do I use that in when combining two columns?

Here is my code where I get a syntax Error:

SELECT
  CONCAT(FORMAT_DATE("%Y-%m-%d", PARSE_DATE('%d%m%Y',
        Date1)), '  ',(PARSE_TIME("%H%M%S",
        Time1)) AS Date_Time
  FROM

Upvotes: 0

Views: 5888

Answers (5)

Ashwini Sadashiv Jagdale
Ashwini Sadashiv Jagdale

Reputation: 1

SELECT  CONCAT(SUBSTRING('170519', 1, 2), ':', SUBSTRING('170519',3, 2), ':', SUBSTRING('170519',5, 2))

this gives you proper ans like 17:05:19

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '09052019' date1, '170519' time1 UNION ALL
  SELECT '10052019', '091020'
)
SELECT PARSE_DATETIME('%d%m%Y%H%M%S', CONCAT(date1, time1)) date_time,
  CAST(PARSE_DATETIME('%d%m%Y%H%M%S', CONCAT(date1, time1)) AS STRING) date_time_as_string
FROM `project.dataset.table`

with result 

Row     date_time               date_time_as_string  
1       2019-05-09T17:05:19     2019-05-09 17:05:19  
2       2019-05-10T09:10:20     2019-05-10 09:10:20

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

I would turn it into a time data type rather than a string:

select parse_time('%H%M%S', '170519')

If it starts out as a number:

select parse_time('%H%M%S', cast(170519 as string))

If you want to format this as a string, you can then format the time. For instance, the following would put this in HH:MM format:

select format_time('%H:%M', parse_time('%H%M%S', cast(170519 as string)))

I am a fan of storing things in the correct type.

Upvotes: 1

Bruno
Bruno

Reputation: 4665

You could do something like this:

SELECT LEFT(c, 2) + ':' + SUBSTRING(c, 3, 2) + ':' + SUBSTRING(c, 5, 2)
FROM
    -- Use your own table in the FROM statement instead of this fake one
    (SELECT '170519' as c ) as q1

Returns 17:05:19

or with your data:

SELECT LEFT(timo, 2) + ':' + SUBSTRING(timo, 3, 2) + ':' + SUBSTRING(timo, 5, 2)
FROM
    table1

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33745

Here is an example that you can try (copy and paste this into the editor in https://console.cloud.google.com/bigquery):

CREATE TEMP FUNCTION InsertColons(s STRING) AS (
  CONCAT(SUBSTR(s, 1, 2), ':', SUBSTR(s, 3, 2), ':', SUBSTR(s, 5, 2))
);

SELECT s, InsertColons(s) AS result
FROM (
  SELECT '123456' AS s UNION ALL
  SELECT '170519'
);

The SUBSTR function selects parts of the string, e.g. the first two characters at ordinal 1, then the first two characters at ordinal 3, then at ordinal 5, before using CONCAT to combine the parts.

Upvotes: 2

Related Questions