CODEWITHSUNDEEP
sqlgoogle-bigquery
Silvana Alvarez Lleras
Silvana Alvarez Lleras

Reputation: 11

Date to Integer YYYMMDD in Standar SQL (BigQuery)

I need to convert a date to an integer with the format YYYMMDD in Bigquery.

I tried with:

PARSE_DATE('%Y%m%d', response_date)

but its not working (Error: No matching signature for function PARSE_DATE for argument types: STRING, DATE. Supported signature: PARSE_DATE(STRING, STRING) at [1:8])

response_date: Date Format

Wanted Result: 20210201 as an integer or string

If anyone know the correct sintax it would be really helpful. Thank you!

Upvotes: 1

Views: 6810

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

Another option (might look funny/silly but works)

select  translate('' || response_date, '-', '')

and then you can do with resulted string whatever cast'ing you need or just leave as is (as string)

... can easily be tested with below query

select  translate('' || current_date(), '-', '')

with output

enter image description here

But obviously - the best way to go is with FORMAT_DATE() as in Gordon's answer :o)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

One method is arithmetic:

select extract(year from response_date) * 10000 + extract(month from response_date) * 100 + extract(day from response_date)

Another method is FORMAT_DATE():

select cast(format_date('%Y%m%d', response_date) as int64)

Upvotes: 2

Related Questions