Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Timestamp Date = item (APEX 4.0)
Timestamp Date = item [message #543352] Tue, 14 February 2012 06:42 Go to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Hello Smile

I'm having a little problem with trying to get a sort of search bar for my report. It's just the one column I want to search (Date) which is a timestamp.
So I've tried adding
TO_CHAR(DATE,'DD-MON-YY') = :P1_DATE
To the where clause, but that doesn't seem to work...
Is there a way to just search the date of a timestamp?

thanks
Re: Timestamp Date = item [message #543360 is a reply to message #543352] Tue, 14 February 2012 06:58 Go to previous messageGo to next message
CajunVarst
Messages: 55
Registered: April 2010
Location: Washington, D.C.
Member
What is the name of the report field you are trying to search?
For P1_Date, what type of page item is this? Is it a date picker or a textbox?

Is the where clause in your report SQL statement? Maybe you can post the sql query you are using for the report?
Re: Timestamp Date = item [message #543362 is a reply to message #543360] Tue, 14 February 2012 07:25 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Well, it's a little complicated, but the source for my report is:
DECLARE
  v_query varchar2(10000);
BEGIN
  IF :P1_QUERY is not null THEN
    v_query := 'select 
  pm.MILESTONE_ID " ",
  TO_DATE(TO_CHAR(pm.MILESTONE_TIME,''DD-MON-YYYY HH24:MI''),''DD-MON-YYYY HH24:MI'') "Date",
  TO_CHAR(pm.MILESTONE_TIME,''HH24:MI'') "Time",
  ''http://ppmc.ch-gva01.serono.com/itg/web/knta/crt/''||P.PPMC_PROJECT_CODE "PPMC Link",
   p.PROJECT_NAME "Project Name",
   p.PL_MUID "Project Leader",
   p.IM_MUID "Information Manager",
  cp.CHECKPOINT_CODE "CP"' ||:P1_QUERY||',
  case when (select count(*)  
    from PMO_MILESTONE_REVIEW pmr  
    where pmr.MILESTONE_ID = pm.MILESTONE_ID
    and pmr.IS_APPROVED = 1) = :P1_COUNT THEN
    ''<font color="green">APPROVED</font>''
    ELSE
    ''<font color="red">REJECTED</font>''
    end "Overall"
from PMO_MILESTONES pm,
     PMO_CHECKPOINT cp,
     PMO_PROJECT P
where pm.PROJECT_ID = p.project_id
and cp.checkpoint_ID = pm.checkpoint_ID
order by pm.milestone_time';
  ELSE
  v_query := 'SELECT 1 FROM dual WHERE 1=0';
  END IF;
  return(v_query);
END;

Then the item P1_QUERY is a query which returns:
,

 decode((select pmr.IS_APPROVED
       from PMO_MILESTONE_REVIEW pmr
       where pmr.MILESTONE_ID = pm.MILESTONE_ID
       and pmr.ROLE_ID = 1),
 1, '<font color="green">Y</font>',
 0, '<font color="red">N</font>',
 '<font color="orange">?</font>') "Ver",

 decode((select pmr.IS_APPROVED
       from PMO_MILESTONE_REVIEW pmr
       where pmr.MILESTONE_ID = pm.MILESTONE_ID
       and pmr.ROLE_ID = 2),
 1, '<font color="green">Y</font>',
 0, '<font color="red">N</font>',
 '<font color="orange">?</font>') "SnR",

 decode((select pmr.IS_APPROVED
       from PMO_MILESTONE_REVIEW pmr
       where pmr.MILESTONE_ID = pm.MILESTONE_ID
       and pmr.ROLE_ID = 3),
 1, '<font color="green">Y</font>',
 0, '<font color="red">N</font>',
 '<font color="orange">?</font>') "Som",

 decode((select pmr.IS_APPROVED
       from PMO_MILESTONE_REVIEW pmr
       where pmr.MILESTONE_ID = pm.MILESTONE_ID
       and pmr.ROLE_ID = 4),
 1, '<font color="green">Y</font>',
 0, '<font color="red">N</font>',
 '<font color="orange">?</font>') "ITA",

 decode((select pmr.IS_APPROVED
       from PMO_MILESTONE_REVIEW pmr
       where pmr.MILESTONE_ID = pm.MILESTONE_ID
       and pmr.ROLE_ID = 5),
 1, '<font color="green">Y</font>',
 0, '<font color="red">N</font>',
 '<font color="orange">?</font>') "ITF",

 decode((select pmr.IS_APPROVED
       from PMO_MILESTONE_REVIEW pmr
       where pmr.MILESTONE_ID = pm.MILESTONE_ID
       and pmr.ROLE_ID = 6),
 1, '<font color="green">Y</font>',
 0, '<font color="red">N</font>',
 '<font color="orange">?</font>') "PMO"


Then I added:
and TO_CHAR(pm.MILESTONE_TIME,''DD-MON-YY'') = :P1_DATE
after
and cp.checkpoint_ID = pm.checkpoint_ID


The item :P1_DATE is a Datepicker with format DD-MON-YY (which isn't the format of the date in the timestamp, I checked the database, which is why I put the TO_CHAR)

thanks

[Updated on: Tue, 14 February 2012 07:29]

Report message to a moderator

Re: Timestamp Date = item [message #543364 is a reply to message #543360] Tue, 14 February 2012 07:32 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
with format DD-MON-YY (which is the format of the date in the timestamp, I checked the database)

What datatype is your pm.milestone time? If it's a date then no, it doesn't have that format. It will always have a time portion. DD-MON-YY is just the way oracle chooses to display it to you unless you supply a format to see the column value. (It's in your settings)

The trunc function is what you're looking for. Apply it to the date field in your table, and the time portion will not be considered. Then you can compare it to a to_date of :P1_DATE with your format.
Re: Timestamp Date = item [message #543367 is a reply to message #543364] Tue, 14 February 2012 07:36 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Sorry c_stenerson, I changed that bit, I meant isn't :/
Re: Timestamp Date = item [message #543371 is a reply to message #543367] Tue, 14 February 2012 07:44 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
What datatype is your pm.milestone time?


The answer to your question depends on this. I hope it's a date rather than a varchar2.Please give us the create table statement for pmo_milestones.
Re: Timestamp Date = item [message #543373 is a reply to message #543371] Tue, 14 February 2012 07:50 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
It's a timestamp

The create table statement is:
CREATE TABLE "ITPMO"."PMO_MILESTONES"
  (
    "MILESTONE_ID"        NUMBER NOT NULL ENABLE,
    "CHECKPOINT_ID"       NUMBER NOT NULL ENABLE,
    "PROJECT_ID"          NUMBER NOT NULL ENABLE,
    "MILESTONE_STATUS_ID" NUMBER NOT NULL ENABLE,
    "MILESTONE_TIME" TIMESTAMP (0),
    CONSTRAINT "PMO_MILESTONES_PK" PRIMARY KEY ("MILESTONE_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE,
    CONSTRAINT "FK_PROJECT_MILESTONE" FOREIGN KEY ("PROJECT_ID") REFERENCES "ITPMO"."PMO_PROJECT" ("PROJECT_ID") ENABLE,
    CONSTRAINT "FK_PROJECT_MILESTONE_STATUS" FOREIGN KEY ("MILESTONE_STATUS_ID") REFERENCES "ITPMO"."PMO_MILESTONE_STATUS" ("MILESTONE_STATUS_ID") ENABLE,
    CONSTRAINT "FK_PROJECT_MILESTONE_CP" FOREIGN KEY ("CHECKPOINT_ID") REFERENCES "ITPMO"."PMO_CHECKPOINT" ("CHECKPOINT_ID") ENABLE
  )
  SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
  (
    INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
  )
  TABLESPACE "USERS" ;

(made using SQL developer, that's just copy paste from the generated DDL)
Re: Timestamp Date = item [message #543375 is a reply to message #543373] Tue, 14 February 2012 07:54 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
And I looked at the TRUNC function, I'm not entirely sure what to put where,

I tried:
and TRUNC(TO_DATE(pm.MILESTONE_TIME,''DD-MON-YY'')) = :P1_DATE
and
and (SELECT TRUNC(TO_DATE(pm.MILESTONE_TIME,''DD-MON-YY''))) = TO_DATE(:P1_DATE, ''DD-MON-YY'')

[Updated on: Tue, 14 February 2012 07:55]

Report message to a moderator

Re: Timestamp Date = item [message #543379 is a reply to message #543375] Tue, 14 February 2012 08:08 Go to previous messageGo to next message
balckbandit5
Messages: 104
Registered: December 2011
Senior Member
Ok I got it:
and TRUNC(pm.MIlESTONE_TIME) = :P1_DATE


Thanks Smile
Re: Timestamp Date = item [message #543380 is a reply to message #543379] Tue, 14 February 2012 08:15 Go to previous message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
And a tip to you right away. Never use to_date on a date/timestamp! to_date is used to convert a string to a date.
Previous Topic: Layout of a report
Next Topic: Application express
Goto Forum:
  


Current Time: Tue Mar 19 05:18:32 CDT 2024