How to safely convert strings to date?

Question

I'm using the DATE function to get the date from three variables for year, month and date, like this:

DATE(year_var, month_var, day_var) AS date_ymd

However, occasionally this would result in an invalid date, e.g., month_var may be 11 and day_var 31. In these cases DATE would fail, as 2022-11-31 is not a valid date.

Instead, I would need to have NULL as the result .

I'm looking for something like SAFE_CAST, but for dates.

Any hints are greatly appreciated!

What date would you like 2022-11-31 to be? – johndk Dec 07 '22 at 13:09 — johndk, Dec 07 '22 at 13:09
As it's an invalid date, I would like date_ymd to be NULL. – Sebastian Dec 07 '22 at 13:10 — Sebastian, Dec 07 '22 at 13:10
I did not understand question. I appologize. – johndk Dec 07 '22 at 13:20 — johndk, Dec 07 '22 at 13:20

score 6 · Accepted Answer · answered Dec 07 '22 at 13:16

6

Use SAFE. prefix for safe function calls.

SELECT SAFE.DATE(2022, 11, 31) AS date_ymd;

answered Dec 07 '22 at 13:16

Jaytiger

11,626
2
5
15

How to safely convert strings to date?

1 Answers1

Linked