Getting the last day of the current month IFNULL in BigQuery-CodePudding

I have an end_date column that I need to map, the problem is that it has some null values and I would like to have the last day of the month in case it's null.

This is what I've been trying but for some reason it doesn't allow me to:

IFNULL(CAST(end_date) AS STRING, LAST_DAY(CURRENT_DATE(), month))

What am I might be doing wrong?

CodePudding user response：

@Jaytiger end_date is DATE type,

Below is more concise one if you want the result as DATE type, I guess.

IFNULL(DATE(end_date), LAST_DAY(CURRENT_DATE(), month))

Example:


SELECT IFNULL(DATE '2022-08-26', LAST_DAY(CURRENT_DATE(), month)) not_null,
       IFNULL(NULL, LAST_DAY(CURRENT_DATE(), month)) `null`,

CodePudding user response：

I managed to solve it, I was using the CAST syntax wrong and I also needed to CAST the last day of the current month as a string too so I could use the IFNULL.

This is how the correct query would look like:

DATE(
    IFNULL(
        CAST(end_date AS STRING),
        CAST(LAST_DAY(CURRENT_DATE(), month) AS STRING)
    )
)