topic Re: Datamart SQL and SYSDATE in Report Server

Datamart SQL and SYSDATE

Anonymous — Wed, 17 Jan 2024 14:17:00 GMT

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

Re: Datamart SQL and SYSDATE

Anonymous — Thu, 18 Jan 2024 05:35:24 GMT

Hi Anonymous ,

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.

Re: Datamart SQL and SYSDATE

Anonymous — Mon, 22 Jan 2024 15:48:08 GMT

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