Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
Didn't need to convert null to a date, just needed to use GETDATE( ) instead of SYSDATE( )
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.