when date field is null, I want to set it to today's date-CodePudding

case when o_s.dbt_valid_to IS NULL then o_s.dbt_valid_to = GETDATE() end Valid_to The code I have now is still returning null, can someone let me know whats the mistake! Full code below

FROM (
    SELECT DISTINCT
        (coalesce(o_s.amount,0) / o_s.term_multiplier_c) * 12 AS Amount,
        o_s.stage_name AS Opportunities_Stage, 
        o_s.id AS ID,
        o_s.name AS Name,
        o_s.license_version_c AS Liscence_Version,
        o_s.dbt_updated_at AS Updated_time,
        o_s.dbt_valid_from AS Valid_from,
        o_s.dbt_valid_to AS Valid_to,
        GETDATE() AS date,
        case when o_s.dbt_valid_to IS NULL then o_s.dbt_valid_to = GETDATE() end Valid_to
        --case when Valid_to IS NULL then Last_date=GETDATE() else Last_date=Valid_to end Last_date
    From{{ref('opportunities_snapshot')}} o_s

    GROUP BY
        o_s.name,
        o_s.amount,
        o_s.stage_name,
        o_s.id,
        o_s.license_version_c,
        o_s.dbt_updated_at,
        o_s.dbt_valid_from,
        o_s.dbt_valid_to,
        o_s.term_multiplier_c
)```

CodePudding user response：

Try NVL: NVL(o_s.dbt_valid_to, GETDATE()) as Valid_to