How to add Previous Date/Time -1 filter in Report Studio

[newtocognos18]

I have this filter currently that I added in via Query Studio:
[Last Submitted Date/Time] in_range ?Last_Submitted_Date_Time?

How do I change it so that it only shows the previous Date (together with time, 12am?) data? Trying to do this in Report Studio as Query is limited in terms of filter.

Tried to search several posts here and websites but just couldn't locate the resolution. :(

I don't mind adding it to the column, but I'm suspecting due to the presence of the time value, the date -1 alone is not enough?

Thanks for the help in advance!

[Lynn]

I have this filter currently that I added in via Query Studio:
[Last Submitted Date/Time] in_range ?Last_Submitted_Date_Time?

How do I change it so that it only shows the previous Date (together with time, 12am?) data? Trying to do this in Report Studio as Query is limited in terms of filter.

Tried to search several posts here and websites but just couldn't locate the resolution. :(

I don't mind adding it to the column, but I'm suspecting due to the presence of the time value, the date -1 alone is not enough?

Thanks for the help in advance!

Casting a date/time to a date will strip off the time portion. The _add_days function lets you subtract one day. If you need to match on 12am specifically then cast it back to a date/time. Your parameter will need to match the data type you are comparing it to.

Code Select Expand


_add_days ( cast ( [Last Submitted Date/Time], date ), -1) = ?Last_Submitted_Date?


[newtocognos18]

Casting a date/time to a date will strip off the time portion. The _add_days function lets you subtract one day. If you need to match on 12am specifically then cast it back to a date/time. Your parameter will need to match the data type you are comparing it to.

Code Select Expand


_add_days ( cast ( [Last Submitted Date/Time], date ), -1) = ?Last_Submitted_Date?


Hey thanks for the help! No error this time and able to run the report.. However, strange that the report has submitted dates that are older than December 10th, all the way to year 2017  :-\ there's a mistake in the code possibly?

Edit: this did not work as earlier I wrongly inserted the code. Error will appear as well.

[newtocognos18]

I self-resolved the issue already...  ;D by adding Last Submitted Date and filtering it with this code:

[Last Submitted Date]=_add_days(current_date, -1)

The Last Submitted Date/Time will still be in the report as that shows the time stamp when the report was exactly submitted by the user (this is important for our compliance team).

Just sharing in case it helps others in the future!