Datamart SQL and SYSDATE

[DLROLLINGS]

Hi 

I am looking at developing a datamart and have pulled in tables from Oracle, I want to write a SQL statement for a table using 

xx_end_date is null or xx_end_date > sysdate 

This is not working on the datamart SQL. Does anyone have a solution to this?

Thanks

[DLROLLINGS]

Didn't need to convert null to a date, just needed to use GETDATE( ) instead of SYSDATE( )

[Anonymous · Accepted solution]

Hi @DLROLLINGS ,

It seems that the SQL statement you provided is not working on the datamart SQL. One possible solution is to use the function to check for null values and then convert the date to a string so it doesn’t try to convert ‘’ to a date.

Below is my table:

The following SQL might work for you:

SELECT 
    CASE 
        WHEN xx_end_date IS NULL THEN '' 
        ELSE CONVERT(varchar, xx_end_date) 
    END 
FROM 
    [dbo].[DimDate] // yourtablename
WHERE 
    xx_end_date IS NULL OR xx_end_date > GETDATE()

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.