Comparing two date columns

User_GEPNO

Hello,

I have a very simple question, but have not been able to find an answer (maybe because its clear to everyone but me): I have a report with two date columns:

"Contact"."Contact Created Date/Time" and  "Contact Custom Obj 9"."Date Created" 

And the thing I need is to apply a filter where it shows only the lines where the dates from the first column are equal to the dates in the second one.

I understand the way is probably via SQL syntax, but my basic sql logic doesnt apply here, since I am not pulling out data from database, but only filtering.

And, do I need to modify the date format by using CAST ..... AS DATE or is it enough to have the dates adjusted in the "data format" tab?

I am grateful for any help

Gianni Ceresa

Hi,

Is that an analysis or a report (BI Publisher)?

Assuming you mean analysis (by the name of your columns), you take your first column and add a filter.

There you check the "Convert this filter to SQL" checkbox.

In the window you have, you can enter the SQL condition you want to use.

Just write your column1 = column2 (using the correct OBIEE syntax like you did in your post: "presentation table"."presentation column").

If your columns are dates, no need to convert anything, the database will do the job just fine. If your columns are something else but both the same something else, again nothing to worry about.

If your 2 columns are 2 different data types, the question would be why, but in that case casting could be needed depending on the situation.

User_GEPNO

Dear Gianni,

thank you for your prompt answer.

1) It is an Analysis, as you assumed

2) I thought exactly what you are pointing out at - I should have mentioned it in the original post - but it doesnt work for me, I get "no result" for

Filters "Contact Custom Obj 9"."Date Created" = "Contact"."Contact Created Date/Time"

But it is not true as you can see, there are many results where those columns are equal:

Maybe I am using the syntax wrong or I forgot some quotation marks, but I dont know.

Thank you for your help

Gianni Ceresa

Are those dates really just dates?

You seeing '22-Dec-20' doesn't mean the real value isn't 2020-12-22 23:53:47.845

And if you have a time component in your dates, there are lot of chances they will not match the comparison as they will probably all have different times.

User_GEPNO

Exactly my point, but how to make sure those are dates - because I have only changed the formatting so it matches, but not the value itself. Is there a workaround or better - how to find out the original value? This is the table when I uncheck "override default formatting"

I am quite skilled with data in excel/other BI software, but have very little experience with OracleBI so I am grateful for any tips.

Thanks

Gianni Ceresa

I don't have a column with a time component handy, but you could try using CASE(<your column> as DATE).

So in your case the filter would become CAST(<column 1> as DATE) = CAST(<column 2> as DATE)

User_GEPNO

Hello Gianni,

I tried using this formula, one way or another, I cannot make it work unfortunately, no results everytime. Do you think there is a mistake in the syntax?

CAST ("Contact Custom Obj 9"."Date Created" as DATE) = CAST ("Contact"."Contact Created Date/Time" as DATE)

Thanks

Gianni Ceresa

Can you look what the physical query looks like in the logs?

Depending on the database used DATE is maybe not without time component, therefore even a CAST isn't changing anything at the real values.

User_GEPNO

Thank you Gianni, I need to take this up internally, because I cannot access the physical query. Once I have our internal response, I will let you know! I will leave this question opened for now.

User_AWXWU

Hi,

You can try creating additional column(hidden), use formula TIMESTAMPDIFF(SQL_TSI_DAY, "Contact Custom Obj 9"."Date Created", "Contact"."Contact Created Date/Time")

whenever the 2 values are same, it returns 0. and u can put a filter in the report for 0.

Thanks,

Swathi