_add_days subtracting days problem

[Primo]

Hi

can anyone tell me why

[Delivery Date] >= _add_days (current_date,-1)

would return records with [Delivery Date] from tomorrow upward rather than from yesterday upward?  If I use

_add_days (current_date,1)

I get the same results.

What am I missing?

[Lynn]

Maybe there is no data in the source with delivery dates of yesterday or today?

You can create a data item in the query with the expression _add_days (current_date,-1) to see what it returns just to check that it is doing what it should, but I see nothing wrong with what you've done and it should come back with yesterday's data.

[Primo]

Hi Lynn

thanks for your reply.  I had tried using a data item, but I've gone over it again to clarify for myself what's happening - and to check that I'd tried everything that I thought I'd tried.

Today is 28th Sept
I can create a DataItem using _add_days (current_date,-1) which, when I include it in the list report as a column, shows as 27 Sep 2010.
1. If I create a filter using
[Delivery Date] >= _add_days (current_date,-1)
I get no records returned that are earlier than 29th Sept.
2. I then changed the filter to use
[Delivery Date] >= [DataItem1]
  I still get no records returned that are earlier than 29th Sept, while the [DataItem1] column in the report shows as 27 Sep 2010.
3. I then changed the filter to use
[Delivery Date] >= 2010-09-27
  and I get records returned from the 27th, 28th and 29th Sept.
4. I have also tried
[Delivery Date] >= current_date
  and that gives me records from the 28th and 29th Sept.

I had thought that it was _add_days (or my understanding of it) which was at fault but number 2. above seems very odd.  Could it be to do with date formats?  I am in the UK, and even though I'm not using dd/mm/yyyy format and the dates are all appearing correctly I'm now wondering if it's something to do with the difference between UK dd/mm/yyyy and American mm/dd/yyyy.  I'll need to check that later, got other things to do right now.

Thanks again for your time.

[Lynn]

So there is actually data out there in the database for delivery dates you're after, and you can get correct results using a hard-coded date and also current_date.

I generally prefer database functions over the Cognos functions, so it might make sense to generate the SQL for your query and play around with it in your database client directly (outside of Cognos). Use your database's native functions instead of _add_days. I would think that if your delivery date column is a date data type then _add_days would still work in any locale, but maybe you're right about the format. I think you'd get a data type mis-match error using _add_days function if delivery date were NOT a date data type.

I think you can remove all that doubt by understanding what works directly against the database and using that as your filter expression. Good luck!

[Primo]

Thanks - that sounds like good advice: come at it from another angle.

I'm going to do that, if I ever figure out what was wrong with this approach I'll reply to this post in case someone else has similar fun.

Thanks again for your help, Lynn.

[Lynn]

Happy to help (or at least TRY to help). Hopefully next time you hear some American-bashing amongst your fellow country men you can point out that we ain't all that bad :)

ha ha. enjoy the rest of your day.

[Primo]

UPDATE:

I tried looking at SQL (not a strength of mine) and I came up with
[Delivery Date] >= current_date - 1 day
This also seems to produce the reult of adding a day rather than subtracting, so I wonder if it's something wrong with how the database interprets the SQL.

Anyway, I've come up with a working alternative (don't laugh)
[Delivery Date] >= _add_days (2010-01-01,_days_between (current_date,2010-01-02))

It's ridiculous, but it works because I'm adding a positive number of days.

BTW The internet's not really the right forum for this as misunderstandings happen all too easily, but most Europeans have no problems with Americans as individuals (quite the reverse).  What you call American-bashing is maybe more America-bashing, or what we call valid criticism  :) 

Enough of that! - Thanks again for your help

All the best

Primo