Split a column in SQL

Question

I am trying to split my Date column into 2 separate columns (DATE & TIME). Currently, the date column has the date with a time stamp, and I need to drop the time stamp or put it into another column. I essentially need to do the opposite of concat.

I haven't tried splitting, because I can't find a function to do so.

Answer 1

I haven't spent any time in bigquery but a quick search says you can cast. Link

 CAST(Datetime AS DATE) will get you the date
 CAST(Datetime AS TIME) will get you the time

Answer 2

if you use the MS SQL SERVER , you can try the below code:

select CAST(dateadd(second,1672531200,'1970-01-01 00:00:00') as time) [time],CAST(dateadd(second,1672531200,'1970-01-01 00:00:00') as date) [date]

Answer 3

Use below simple approach (BigQuery Standard SQL)

SELECT ts, DATE(ts), TIME(ts)
FROM your_table   

you can "test/play" using below

WITH your_table AS (
  SELECT CURRENT_DATETIME() AS ts
)
SELECT ts, DATE(ts), TIME(ts)
FROM your_table    

with output

Answer 4

Using the Google bigquery dialect of SQL that you're using, these two functions will get the parts of the datetime that you want:

EXTRACT(columnName,DATE)

and

EXTRACT(columnName,TIME)

These will return DATE and TIME results, respectively.

Documentation for this function is here: https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#extract

Answer 5

Below should work:

Select extract(date from datetime)

&

Select extract (time from datetime)