BigQuery standard SQL error, invalid timestamp when date field is not touched at all

Question

The answer here doesn't help me. Although I am having similar issue in a rather simple update query, which looks like below (idea is add sha-256 encoding to legacy records).

UPDATE 
'customer.customers` 
SET emailHashCode = (SELECT 
TO_HEX(SHA256(e.emailAddress))
FROM
`customer.customers`,
UNNEST(emailAddresses) AS e LIMIT 1)
WHERE emailHashCode IS NULL

I get this error:

Cannot return an invalid timestamp value of -62135600400000000 microseconds relative to the Unix epoch. The range of valid timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]; error in writing field dateOfBirth

There's a dataOfBirth field in the table, but its not being touched. Curious if something basic is missing? Thanks for any clue!

Answer 1

Before jumping to your issue with timestamp - first, I wanted to mention that most likely your initial query is not correct - I would recommend you to consider below one instead (assuming that emailAddresses is repeated record with emailAddress field in it)

UPDATE 'customer.customers` 
SET emailHashCode = (
  SELECT TO_HEX(SHA256(e.emailAddress))
  FROM UNNEST(emailAddresses) AS e 
  LIMIT 1
)
WHERE emailHashCode IS NULL

So, now back to your question: as Elliott has mentioned - you need to update the invalid timestamps as part of the same query. I haven't tested - but try below

UPDATE 'customer.customers` 
SET emailHashCode = (
  SELECT TO_HEX(SHA256(e.emailAddress))
  FROM UNNEST(emailAddresses) AS e 
  LIMIT 1
), dateOfBirth = SAFE_CAST(dateOfBirth AS TIMESTAMP)
WHERE emailHashCode IS NULL

Answer 2

I had the same issue, easiest thing is to fix all the rows with bad dataOfBirth values.

I used SAFE_CAST to do this, try something like:

SAFE_CAST(dataOfBirth AS STRING) AS dataOfBirthString

Hope that helps!