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
need in urgent...does any body have an idea about this?
I'm assuming this is relational, so you can use this:
[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?
Quote from: PaulM on 07 Feb 2010 01:25:07 AM
I'm assuming this is relational, so you can use this:
[Date]>=#prompt('Date','date','','_make_timestamp(extract(year,','','),1,1)')# and
[Date]<=#prompt('Date','date')#
Quote from: laks on 07 Feb 2010 01:37:46 AM
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?
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.
thx a lot paulM....now i got u