OraFAQ Forum: Application Express, ORDS & MOD_PLSQL

Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Timestamp Date = item (APEX 4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Timestamp Date = item [message #543352]

Tue, 14 February 2012 06:42

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Hello

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

Report message to a moderator

Re: Timestamp Date = item [message #543360 is a reply to message #543352]

Tue, 14 February 2012 06:58

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?

Report message to a moderator

Re: Timestamp Date = item [message #543362 is a reply to message #543360]

Tue, 14 February 2012 07:25

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

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.

Report message to a moderator

Re: Timestamp Date = item [message #543367 is a reply to message #543364]

Tue, 14 February 2012 07:36

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Sorry c_stenerson, I changed that bit, I meant isn't :/

Report message to a moderator

Re: Timestamp Date = item [message #543371 is a reply to message #543367]

Tue, 14 February 2012 07:44

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.

Report message to a moderator

Re: Timestamp Date = item [message #543373 is a reply to message #543371]

Tue, 14 February 2012 07:50

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)

Report message to a moderator

Re: Timestamp Date = item [message #543375 is a reply to message #543373]

Tue, 14 February 2012 07:54

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

balckbandit5
Messages: 104
Registered: December 2011

Senior Member

Ok I got it:

and TRUNC(pm.MIlESTONE_TIME) = :P1_DATE

Thanks

Report message to a moderator

Re: Timestamp Date = item [message #543380 is a reply to message #543379]

Tue, 14 February 2012 08:15

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.

Report message to a moderator

Previous Topic:	Layout of a report
Next Topic:	Application express

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 12 20:18:11 CST 2024