Displaying data between dates

[Sreeni P]

Hi,

i have one date prompt, when i'm selecting any perticular date from that prompt(ex:3 march 2009),it should dispaly the values between (1 jan 2009 to 3 march 2009)...here the '1 jan' is a default for any year...

EX:  if i select 5 feb 2010 , then it should display the values between 1 jan 2010  and 5 feb 2010

[Sreeni P]

need in urgent...does any body have an idea about this?

[CognosPaul]

I'm assuming this is relational, so you can use this:

Code Select Expand

[Date]>=#prompt('Date','date','','_make_timestamp(extract(year,','','),1,1)')# and
[Date]<=#prompt('Date','date')#

[laks]

You can use this in your detail filter

Date_date_item between to_date(to_char(?Date?,'YYYY')||'-01-01','YYYY-MM-DD') and ?Date?

[Sreeni P]

I'm assuming this is relational, so you can use this:

Code Select Expand

[Date]>=#prompt('Date','date','','_make_timestamp(extract(year,','','),1,1)')# and
[Date]<=#prompt('Date','date')#

You can use this in your detail filter

Date_date_item between to_date(to_char(?Date?,'YYYY')||'-01-01','YYYY-MM-DD') and ?Date?

Thx for the reply , can u elaborate in briefly that how would i get the result using this scenarios?

[CognosPaul]

Your requirements are to return YTD for the prompted date. The easiest, but not the only, way to accomplish would be to create a timestamp that uses the year of the prompted date and 1 for both month and day.

Lets say the user were to select 2010-02-08. In my example the prompt would then become:

[Date]>=_make_timestamp(extract(year,'2010-02-08'),1,1) and [Date]<='2010-02-08'

Which would then resolve to [Date]>='2010-01-01' and [Date]<='2010-02-08'.

In Laks example, Oracle specific, the filter would be:

[Date_date_item] between to_date(to_char('2010-02-08','YYYY')||'-01-01','YYYY-MM-DD') and '2010-02-08'

Which would then resolve to
[Date_date_item] between '2010-01-01' and '2010-02-08'

In both cases you are using the prompted value to find the first day of that year.

[Sreeni P]

thx a lot paulM....now i got u