Get data from previous day 6.30am to current day6.30am each day

[cognos74]

Hi,

in one of my report I need to get data from previous day 6.30am to current day 6.am each day.  not sure how to get that can any one help me in this.

my date fields is date and time like: Feb 22, 2017 4:07:00 AM

Thank you..

[dougp]

Well, in MS SQL I'd do this:

Code Select Expand

select dateadd(minute, cast(6.5 * 60 as int), cast(cast(getdate() as date) as datetime)) as Yesterday
, dateadd(minute, cast(6.5 * 60 as int), dateadd(day, -1, cast(cast(getdate() as date) as datetime))) as Today

So in Cognos:

Code Select Expand

cast(_add_minutes ( _add_days (current_date, -1 ), 6.5 * 60 ), timestamp)

Code Select Expand

cast(_add_minutes ( current_date, 6.5 * 60 ), timestamp)

The cast function may not be needed.

I'd like to do this using macros to take the load off of the database server, but I haven't got it yet and the database server is plenty fast at this.