Date and Time prompt issue

[COGNOSBC]

Hi,

my report is based on sql.  I have a date prompt  like between ?startdatd? and ?enddate? but I just noticed that it is not only date prompt it is date and time prompt but I have to take out the time from the date prompt.  not sure how to use date 2 string or any other date functions to date filter.  because when  I run the report for example Jun 1st to Jun 5th etc..  it is returning data from Jun 1st to jun 4th because of the time included in the prompt.

please any one can help.

Thanks,

[MFGF]

Hi,

Have you tried replacing it with a date prompt rather than a date and time prompt? Do your dates have embedded timestamps or are the times all set to 00:00:00?

MF.

[CognosPaul]

You could also try using a prompt macro to force it to only use the date portion:

#prompt('startdate','date')#

[COGNOSBC]

Can I use #prompt('startdate','date')# in the detail filter? because report is based on SQL but I have to add prompt in the Query.

becasue based on the date column I have to add 4 optional prompts so for that I am trying to add the filter in the query not in the sql.

Thanks,
Jyothi.

[CognosPaul]

Macros can also be used directly in the SQL. They have a default parameter which can make it optional.

This means you can do something like the following:

Code Select Expand

select
  *
from
  table t
where
      #prompt('startDate','date','1=1','t.date >= ')#
  and #prompt('endDate','date','1=1','t.date <= ')#


If the user doesn't select either start or end date, it will be replaced with 1=1. If they do select a value, then it will be preceded by t.date >= 

[COGNOSBC]

Hi PaulM

in my prompt page I have 5 optional radio button promts like below:

Proir day
prior 3 days
prior 4 days
prior week (last sunday to this monday)
custom(means based on entered startdate and enddate)

could you please explain me how to put this logic in the macro prompt in sql not in the query,

Thanks again

[CognosPaul]

The SQL will need to be fixed for your flavor of SQL. I'm also making a lot of assumptions about your database and fields. This is just to point you in the right direction.

Assuming your sql looks like:

select
  t.whatever
  , t.whatever2
from
  table t
where
  ...

First, set the static choices of your radio to
Use: trunc(t.date) = trunc(sysdate)
Disp: Priod Day


Use: trunc(t.date) between trunc(sysdate)-1 and trunc(sysdate)
disp: last 2 days


Use: trunc(t.date,'D') = trunc(trunc(t.date,'D')-1,'D')
Disp: Last Week

Use:
Disp: Custom

Note, the use value for the custom is blank. This is intentional, as the prompt will be forced to use the default parameter, which will contain the start/end dates.

The where clause should now be:

where
#prompt('DateRange','token','trunc(t.date) between to_date('+prompt('StartDate','date',timestampMask($current_timestamp,'yyyy-mm-dd'))+','+sq('yyyy-mm-dd')+') and to_date('+prompt('EndDate','date',timestampMask($current_timestamp,'yyyy-mm-dd'))+','+sq('yyyy-mm-dd')+')' )#

If the user selects something from the radio, it will be what you put into static choices. If they select the custom group, it will use what's in the third parameter of the macro, ultimately creating the trunc(date) between whatever fragment of the SQL. It's worth noting that the date prompts also have default values returning today's date.

This was all in Oracle syntax, but the logic is sound and it shouldn't be too difficult to migrate this into any other SQL variant.