SAP Universe filter needed for previous week. Based on a datetime object

[careymw]

I am trying to create a previous week filter in the universe to use to schedule a weekly report. I have a datetime field and it's giving me issues.

PSSI3_DM.AGENTACTIVITYLOG.STATUSDATETIME

I thought I could just do something like this.

PSSI3_DM.AGENTACTIVITYLOG.STATUSDATETIME >= dateadd(wk, datediff(wk, 7, getdate()), 0)
and
PSSI3_DM.AGENTACTIVITYLOG.STATUSDATETIME <= dateadd(wk, datediff(wk, 7, getdate()), 6)

or even this....
PSSI3_DM.AGENTACTIVITYLOG.STATUSDATETIME BETWEEN dateadd(wk, datediff(wk, 7, getdate()), 0) AND dateadd(wk, datediff(wk, 7, getdate()), 6)

How do you account for the time?
Previous week would be 11/24/2013 12:00:00 AM to 11/30/2013 11:59:00 PM

 

[SkipVought]

hi,

Previous week would be 11/24/2013 12:00:00 AM to 11/30/2013 11:59:00 PM

Really? What happens to 11/30/2013 11:59:01 PM? You have a ONE MINUTE HOLE in your assumption!

Actually, the previous week is

where [WhateverDate] >= 11/24/2013 12:00:00 AM
  and [WhateverDate] <  12/01/2013 12:00:00 AM

Which is IDENTICAL to

where [WhateverDate] >= 11/24/2013
  and [WhateverDate] <  12/01/2013

But of course the date strings would need to be converted to date/tiem values using whatever SQL syntax is appropriate for your db.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[careymw]

Thanks!!! Good point and good catch.

Are there Any ideas or suggestions on where to start with the SQL syntax. To convert date/time? I've only worked with date objects in the past.

 

[SkipVought]

What db?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]