BigQuery SQL - Change 0 to NULL, change NUMERIC to STRING type and PARSE DATETIME (single query)-CodePudding

I have a requirement where the current type is NUMERIC but I need it to be DATETIME, I am currently working with the data owner to get them to convert the data type so I will not have to perform these steps.

As stated above, the NUMERIC type needs to be changed to DATETIME but I have an issue where zero values exist. I have currently written two SQL queries to achieve the result but I was hoping to be able to do this in a single query.

See the below two queries:

Query 1

SELECT 

MATERIAL,DESC,NUMBER,

CASE
    WHEN START_ACTUAL = 0 THEN NULL
    ELSE START_ACTUAL
    END AS START_ACTUAL,

CASE
    WHEN END_ACTUAL = 0 THEN NULL
    ELSE END_ACTUAL
    END AS END_ACTUAL,

FROM `SAMPLEFILE1`

Query 2

SELECT

MATERIAL,DESC,NUMBER,

PARSE_DATETIME('%Y%m%d%H%M%S', CAST(START_ACTUAL AS STRING)) AS START_ACTUAL,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(END_ACTUAL AS STRING)) AS END_ACTUAL,

FROM `SAMPLEFILE1_VIEW`

I'm sure someone will put me right straightaway and it is very simple :)

Thanks in advance

CodePudding user response：

You could wrap your case statements in the CAST and PARSE_DATETIME, but you could just use nullif:

SELECT

MATERIAL,DESC,NUMBER,

PARSE_DATETIME('%Y%m%d%H%M%S', CAST(NULLIF(START_ACTUAL,0) AS STRING)) AS START_ACTUAL,
PARSE_DATETIME('%Y%m%d%H%M%S', CAST(NULLIF(END_ACTUAL,0) AS STRING)) AS END_ACTUAL,

FROM `SAMPLEFILE1_VIEW`