CODEWITHSUNDEEP
sqldatetimegoogle-bigquerysql-update
James
James

Reputation: 91

Change month and day only in BigQuery

Is there an equivalent to DATEFROMPARTSin BigQuery? I'm trying to change only the month and day in my timestamp, not the year.

Here's my table in DATETIME:

BirthYear 
2014-12-12T00:00:00
2015-01-07T00:00:00

I want to change only the month and day but keep the year. For example change the bottom row to: 2015-04-01T00:00:00

The following query works in MS SQL and I'm trying to rewrite it in BigQuery:

UPDATE `table` SET BirthYear = DATEFROMPARTS(BirthYear, 04, 01) WHERE BirthYear IS NULL

Upvotes: 0

Views: 1505

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173121

BigQuery equivalent of datefromparts(year(birthdate), 4, 1) is

date(extract(year from BirthYear), 4, 1)

also, if you need it to "convert" back to datetime you might want to use as below

datetime(date(extract(year from BirthYear), 4, 1))

Upvotes: 3

GMB
GMB

Reputation: 222632

For the following SQL Server expression:

datefromparts(year(birthdate), 4, 1)

In BigQuery, you could do this with datetime_trunc() and datetime_add():

datetime_add(datetime_trunc(birthdate, year), interval 4 month)

This gives you a datetime value. You can use date_trunc() and date_add() if you want to handle dates instead.

Upvotes: 1

Related Questions