peoplesoft.wikidot.com Open in urlscan Pro
107.20.139.176  Public Scan

Form analysis
2 forms found in the DOM

dummy

<form id="search-top-box-form" action="dummy" class="input-append">
  <input id="search-top-box-input" class="text empty search-query" type="text" size="15" name="query" value="Search this site" onfocus="if(YAHOO.util.Dom.hasClass(this, 'empty')){YAHOO.util.Dom.removeClass(this,'empty'); this.value='';}"><input
    class="button btn" type="submit" name="search" value="Search">
</form>

GET dummy.html

<form action="dummy.html" method="get" onsubmit="WIKIDOT.modules.NewPageHelperModule.listeners.create(event);">
  <input class="text" name="pageName" type="text" size="15" maxlength="128" style="margin: 1px">
  <input type="submit" class="button" value="Create page" style="margin: 1px;">
</form>

Text Content

PEOPLESOFT WIKI


Create account or Sign in





WIKI

 * Welcome
 * Tags
 * All Articles
 * Recent changes
 * RSS Feed
 * Follow on Twitter
 * LinkedIn Group
 * PeopleSoft Services
 * Search
 * Screencasts
 * Recommended Books
 * Contact
 * My Blog


PARTNERS


MEMBERSHIP

 * Become a Member
 * Contributing to the Wiki


FORUMS

 * Forum
 * Recent posts
 * Hints, Tips and Gotchas
 * Ask for Help
 * General Discussion
 * Employment Opportunities

--------------------------------------------------------------------------------

 * Seen a new PeopleSoft implementation? Report it now – asterlan.com


TOP ARTICLES

 * Application Engine
 * Auditing User Profiles
 * Batch Scheduling
 * Campus Solutions Tables
 * CI Development FAQ
 * Component Interfaces
 * CI Based Web Services
 * Consuming a Web Service
 * Copying Rowsets
 * Data Archive Manager
 * Debugging and Tracing
 * Definition Security
 * Designing Pages
 * Email Message Catalog
 * Integration Broker
 * Managing Indexes
 * Migrate Project
 * PeopleTools Security
 * Process Scheduler
 * Report Manager
 * Skilling up for Fusion
 * System Process Requests
 * Testing Web Services
 * Text Editors
 * Tracing App Designer
 * XML (BI) Publisher


RESOURCES

 * Great Free Software
 * PS Chrome Helper Extension
 * Oracle PeopleBooks
 * PeopleSoft Resources
 * PeopleSoft Web Search
 * Useful Web Sites


LINKS

 * mcAMDOIS - CAPI
 * Grey Sparling's Blog
 * PSST0101
 * The PeopleSoft DBA
 * PeopleSoft Tipster
 * PeopleSoft Corner
 * Jim's PeopleSoft Journal
 * PeopleSoft Tutorial
 * Blogging about Oracle Applications
 * PeopleSoft Learnings
 * PeopleSoft Career
 * PeopleSoft SQR
 * PeopleTools Blog
 * PeopleSoft Support Tips
 * Extra Hot
 * Working Scripts
 * PeopleSoft / Oracle Tips
 * ERP Wizard
 * PSFT_PP
 * Cedar Hills Group
 * Remote DBA



--------------------------------------------------------------------------------



--------------------------------------------------------------------------------

Writing a Date expression in PS Query
Forum » Forum / Help » Writing a Date expression in PS Query
Started by: Steve (guest)
Date: 11 Feb 2009, 20:17
Number of posts: 40
RSS: New posts
Unfold All Fold All More Options
Edit Title & Description Stickness Lock Thread Move Thread

page 1 of 212next »
Fold
Writing a Date expression in PS Query
Steve (guest) 11 Feb 2009, 20:17

Hi,

I'm trying to write an expression in PSQuery which will subtract two dates from
each other and return the difference.

If I write the following in SQL it works fine, Select ROUND(A.EFFDT -
A.LASTUPDDTTM) from PS_JOB a.

However if I try to insert this as an expression in PSQuery I get various error
messages about Date formats beingincorrect.

I think the problem is the dynamic SQL that's created by PSQuery, i tried added
a To_Date conversion into the expression but PS still wirting the SQL as
follows:-

SELECT TO_DATE( TO_CHAR(A.EFFDT,'YYYY-MM-DD'),'YYYY-MM-DD') - (TO_DATE(
TO_CHAR(CAST((A.LASTUPDDTTM) AS
TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD-HH24-MI-SS-FF'))

If I don't include the .SS.FF in the mask I receive an error about the date
picture, ORA1830 error.

Does anybody have any experience of using the last update field on job in HRMS.
Or can anybody suggest how I can write the expression in ps query.

Thanks
Steve

Reply Options

Unfold Writing a Date expression in PS Query by Steve (guest), 11 Feb 2009,
20:17
Fold
Re: Writing a Date expression in PS Query
Praj 12 Feb 2009, 03:41

Hi,

I have written something similar in PS Query to return the processing time for a
process/job in the process request table. This query returns the process
instance, process name, operator ID, run status, run control ID and processing
time (which is process end date/time - process begin date/time expressed as
seconds).

What you have explained is very close. My processing time expression looks like
this:

You can ignore the case statement, the key to this is really the following:

to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60))

This gives the difference in seconds. However if you just want the answer in
days it would be just:

to_char(round(ENDDTTM - BEGINDTTM))

Which is pretty much exactly what you have. Note the to_char is to format the
output in character format which PSQuery seems to handle a lot better than
numeric format.

In terms of formatting instead of messing around with dates I've just used a
character expression as I just want to display the number of seconds. I figure
you just want the output as the number of days?

My Query SQL looks like this:

SELECT A.PRCSINSTANCE
 , A.PRCSNAME
 , TO_CHAR(A.RUNDTTM
 ,'YYYY-MM-DD-HH24.MI.SS."000000"')
 , A.OPRID
 , A.RUNSTATUS
 , A.RUNCNTLID
 , (CASE WHEN ENDDTTM IS NULL THEN 'Not Available' 
    ELSE to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60)) END) 
   AS PROCESSING_TIME 
  FROM PSPRCSRQST A


If you try to get the output in date format you'll have all kinds of problems.
For example what do you display as the year and month? What about the timestamp?
I've found that I really just want the difference expressed as a year, month,
day, hour, minute or second value and not in date format at all.

Hope that helps.

Reply Options

Unfold Re: Writing a Date expression in PS Query by Praj, 12 Feb 2009, 03:41
Fold
Re: Writing a Date expression in PS Query
Steve (guest) 12 Feb 2009, 08:23

Thanks for your reply it's appreciated :).

I've modified your expression above to use my field names, it looks as follows:-

to_char(round(a.effdt - A.LASTUPDDTTM))

I've set the expression type to character and the length to 15. When I run it I
receive the following error:-

A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=1722, Message=ORA-01722:
invalid number (50,380)

When I look at the SQL that's been inserted by PSQUERY (View SQL Tab) i see the
following for this expression:-

to_char(round( TO_CHAR(A.EFFDT,'YYYY-MM-DD') - TO_CHAR(CAST((A.LASTUPDDTTM) AS
TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'))),

Any more ideas gratefully received, it's driving me mad. Especially as when you
do the same line in SQL it works fine.

Thanks
Steve

Reply Options

Unfold Re: Writing a Date expression in PS Query by Steve (guest), 12 Feb 2009,
08:23
Fold
Re: Writing a Date expression in PS Query
Praj 13 Feb 2009, 04:49

Hi,

I can't seem to be able to replicate your scenario where the expression you're
using as a field:

to_char(round(a.effdt - A.LASTUPDDTTM))

Suddenly turns to:

to_char(round( TO_CHAR(A.EFFDT,'YYYY-MM-DD') - TO_CHAR(CAST((A.LASTUPDDTTM) AS
TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')))

In the View SQL tab. Are you using any criteria on your expression field? Can
you try removing all fields in your query, then going to the expressions tab,
finding your expression and selecting use as a field again?

Sometimes PS Query caches the expression and you get the wrong version of it. It
should put the expression exactly in line into your SQL.

Another thing you can try is:

 * Create a new private query
 * Add the record PSXLATITEM
 * Select the fields, FIELDNAME, FIELDVALUE, EFFDT, LASTUPDDTTM
 * Create a new expression: to_char(round(LASTUPDDTTM - EFFDT)) DAYS as a
   character with a length of 15 and choose select use as field to make it the
   fifth field.
 * Have a look at your view SQL and see if that looks right/runs? You might get
   a maximum rows exceeded error but that's ok, the field should show the number
   of days.

Reply Options

Unfold Re: Writing a Date expression in PS Query by Praj, 13 Feb 2009, 04:49
Fold
Re: Writing a Date expression in PS Query
Steve (guest) 18 Feb 2009, 17:53

Hi,

Sorry it's taken me a few days to reply.

I couldn't get PSQuery to insert this expression without all the other
associated code. I gave up in the end and ran it directly against the database
via SQL. The end user only wanted a one report so it wasn't too much of a
problem.

Thanks for you assistance.

Reply Options

Unfold Re: Writing a Date expression in PS Query by Steve (guest), 18 Feb 2009,
17:53
Fold

QUERY (guest) 24 Nov 2015, 23:59

Hi, I am trying to get the difference between two datetime fields. The field is
formatted to 11/20/2015 12:11:49PM (date & time). I want to get the # of days
between DTTM_CREATED.

Thanks.

Reply Options

Unfold by QUERY (guest), 24 Nov 2015, 23:59
Fold
Re: Writing a Date expression in PS Query
john (guest) 21 Aug 2009, 09:15

I am using PSquery to calculate the number of people who have send their resume
to the company within a year period. How could I do that using date format in
PSQuery?

also the number of resume received at the 31 of each month….

Thanks for your help

Reply Options

Unfold Re: Writing a Date expression in PS Query by john (guest), 21 Aug 2009,
09:15
Fold
Re: Writing a Date expression in PS Query
Praj 26 Aug 2009, 05:20

Here's an example that's similar to yours that may help.

Find all user profiles that were updated in the last year.

To do this in SQL:

select OPRID, LASTUPDDTTM
from PSOPRDEFN
where LASTUPDDTTM between (sysdate - 365) and sysdate
order by LASTUPDDTTM desc;


So find any user where the last update date/time LASTUPDDTTM is greater than or
equal to 365 days (1 year) back from the current date, up until the current
date.

Here's how you would create this expression in PS Query.



Note that I've replaced sysdate with meta-SQL - %CurrentDateTimeIn and
%AddDate(%CurrentDateTimeIn, -365) to go back 365 days.

For the second part, you might want count all user profiles that were updated in
the last year, grouped by each month. Here's the SQL you would write to do this:

select to_char(LASTUPDDTTM, 'Month') as MONTH, count(distinct OPRID)
from PSOPRDEFN
where LASTUPDDTTM >= (sysdate - 365) and LASTUPDDTTM <= sysdate
group by to_char(LASTUPDDTTM, 'Month');


The key here is the use of to_char to convert the month part of the LASTUPDDTTM
field to a month. Here's how you would create the expression:



Add this as a field, and perform a count on the OPRID field.

Last edited on 26 Aug 2009, 05:40 by Praj Show more

Reply Options

Unfold Re: Writing a Date expression in PS Query by Praj, 26 Aug 2009, 05:20
Fold

Susan (guest) 21 Sep 2010, 18:15

We need to select those who are having increment of 5 year anniversaries (5, 10,
15, etc). This worked before we converted from Informix to Oracle. Now we can't
get the selection criteria to work.

The anniversary is derived using the expression:
TO_NUMBER(SUBSTR(TO_CHAR(CURRENT_DATE,'YYYY-MM-DD'),1,4)) -
TO_NUMBER(SUBSTR(A.CMPNY_SENIORITY_DT,1,4))
The expression type is Number with Length = 4. The anniversary displays
correctly.

We run into trouble when trying to create selection criteria where the
expression is equal to 5. The error message is: Error in running query because
of SQL Error, Code=1722, Message=ORA-01722: invalid number (50,380). Both the
left and right side should be considered numeric. This also failed when the
expression was defined as character (length 4).

View SQL shows the following:
AND TO_NUMBER(SUBSTR(TO_CHAR(CURRENT_DATE,'YYYY-MM-DD'),1,4)) -
TO_NUMBER(SUBSTR( A.CMPNY_SENIORITY_DT,1,4)) = 5

Reply Options

Unfold by Susan (guest), 21 Sep 2010, 18:15
Fold

Praj 21 Sep 2010, 22:17

That syntax looks and seems to work fine in Oracle however, I'm not sure about
the =5 at the end of the view SQL? I figure that was you providing an example of
the result and not the actual SQL?

One thing that might be causing it is if there are any cases where the
A.CMPNY_SENIORITY_DT is invalid? It could simply be some bad data and not the
SQL syntax. Perhaps in your where clause include WHERE A.CMPNY_SENIOR_DT <=
sysdate or equivalent just to check, if it works then data could be the culprit.

Another option if you do think it is syntax related is to use meta-SQL to expand
the SQL out correctly. For example an expression like this:

%Truncate((%DateDiff(ASOFDATE, %CurrentDateIn) / 365), 0)


This subtracts the current date from the seniority date using %DateDiff which
returns a value in days. It then divides by 365 (days) truncates off the decimal
to return the whole value in years.

Hope that helps.

Reply Options

Unfold by Praj, 21 Sep 2010, 22:17
Fold

Susan (guest) 28 Sep 2010, 16:22

Praj, thank you for your suggestions. The "=5" was the selection criteria. We
wanted to find everyone who had been with the company 5 years. This was not a
data issue.

The solution was to create 2 expressions - one to display the anniversary and
the other to use as selection criteria. Both were defined as Number and neither
had any decimals; one expression has a length of 3 the other has a length of 4.
The expression with the length of “4” is used to display the anniversary in the
query. The expression with the length of “3” is used for the selection criteria.
Both expressions had the code:
TRUNC(TRUNC(MONTHS_BETWEEN (SYSDATE, TO_DATE(
TO_CHAR(A.CMPNY_SENIORITY_DT,'YYYY-MM-DD'),'YYYY-MM-DD'))) / 12)

SQL Errors occur when the expression with the length of “3” is used for display
or the length of “4” is used for the selection criteria. It seems to me that
this is an Oracle bug. The good news is that we got the query to work.

Reply Options

Unfold by Susan (guest), 28 Sep 2010, 16:22
Fold

Praj 29 Sep 2010, 10:32

Good to hear you got it all working, thanks for posting the solution.

Reply Options

Unfold by Praj, 29 Sep 2010, 10:32
Fold

Paul (guest) 14 Mar 2011, 20:56

Hi Praj,

I'm trying to edit a query that uses a criteria on the year of the most current
Effdt. For example, I want data as of 12/31/YEAR where I have a prompt for the
year. So that every time the query is ran, it runs for 12/31 of the selected
year (2008, 2010, 2011, etc.). Currently, the query has a criteria that prompts
for the year and uses the following syntax:

'31-DEC-'|| DECODE(:1,' ',TO_CHAR(SYSDATE,'YYYY'),:1)

Any thoughts on how to tune this so that I don't get the following error:

ORA-01858: a non-numeric character was found where a numeric was expected

Any help is appreciated. Thanks! :)

Reply Options

Unfold by Paul (guest), 14 Mar 2011, 20:56
Fold

Praj 14 Mar 2011, 23:47

Assuming the prompt for YEAR is bind :1, try the following expression in your
query:

to_date('31-DEC-' ||:1, 'DD-MM-YYYY')


Reply Options

Unfold by Praj, 14 Mar 2011, 23:47
Fold

Abhishek (guest) 19 Aug 2011, 08:55

to_date(A.HRS_OFR_DT_EXP)-to_date(A.OFFER_DT)
Its Working..

Reply Options

Unfold by Abhishek (guest), 19 Aug 2011, 08:55
Fold
Re: Writing a Date expression in PS Query
John Christy 27 Sep 2011, 08:20

How to incorporate the below code in ps query which comes in the where clause..

(C.version_dt = select max(D.version_dt) from ps_run_cntrl_tbl D where C.EmplId
= D.EmplId and C.Roleuser = D.Roleuser or C.version_dt is null)

Thanks in Advance..

Reply Options

Unfold Re: Writing a Date expression in PS Query by John Christy, 27 Sep 2011,
08:20
Fold

Mary (guest) 19 Jan 2012, 00:16

This seems to work when comparing SYSDATE with BIRTHDATE; for example, if you
need to find individuals under or over a certain age (only specific to the year,
not month or day). Note: You may need to subtract one (year) to stay at or below
their current age
(TO_NUMBER(SUBSTR(TO_CHAR(CURRENT_DATE,'YYYY-MM-DD'),1,4)))-(TO_NUMBER(SUBSTR(A.BIRTHDATE,1,4)))
the result is the sysdate year - birthdate year (=age they will become this
year)

Thanks to all for your help!

Reply Options

Unfold by Mary (guest), 19 Jan 2012, 00:16
Fold

Steve (guest) 14 Mar 2012, 15:44

Hi, I'm not a programmer but I'm trying to automate a PS query by entering
criteria to select the effective date between the start and end of the previous
month. Is there any way to do this?

Any assistance is much appreciated. Thanks,

Reply Options

Unfold by Steve (guest), 14 Mar 2012, 15:44
Fold

Peggy (guest) 27 Mar 2012, 13:40

Hi, I'm not a programmer but I'm to automate a PS query by entering
criteria/expression to only select employees 90 days after hire date. Any
assistance you can provide is appreciated.

Reply Options

Unfold by Peggy (guest), 27 Mar 2012, 13:40
Fold

Praj 7 May 2012, 20:48

Are you able to post the SQL of the query you have written so far and where you
are stuck?

Reply Options

Unfold by Praj, 7 May 2012, 20:48
Fold

Lew (guest) 4 May 2012, 16:58

I'm trying to write an expression in PS Query that will return the current date.
Any help please, thanks.

Reply Options

Unfold by Lew (guest), 4 May 2012, 16:58
Fold

GBELL (guest) 7 May 2012, 16:37

SYSDATE

Reply Options

Unfold by GBELL (guest), 7 May 2012, 16:37
Fold

Praj 7 May 2012, 20:48

Yep, or to be database specific, use an expression with %CurrentDateTimeIn. If
you scroll up on this forum thread you'll see an example of it in use.

Reply Options

Unfold by Praj, 7 May 2012, 20:48
page 1 of 212next »
New Post
Permanent Link Edit Delete
/forum/t-129686/writing-a-date-expression-in-ps-query#post-



Help  | Terms of Service  | Privacy  | Report a bug  | Flag as objectionable
Powered by Wikidot.com
Unless otherwise stated, the content of this page is licensed under Creative
Commons Attribution-ShareAlike 3.0 License










Click here to edit contents of this page.
Click here to toggle editing of individual sections of the page (if possible).
Watch headings for an "edit" link when available.
Append content without editing the whole page source.
Check out how this page has evolved in the past.
If you want to discuss contents of this page - this is the easiest way to do it.
View and manage file attachments for this page.
A few useful tools to manage this Site.
See pages that link to and include this page.
Change the name (also URL address, possibly the category) of the page.
View wiki source for this page without editing.
View/set parent page (used for creating breadcrumbs and structured layout).
Notify administrators if there is objectionable content in this page.
Something does not work as expected? Find out what you can do.
General Wikidot.com documentation and help section.
Wikidot.com Terms of Service - what you can, what you should not etc.
Wikidot.com Privacy Policy.
5042 days ago
5042 days ago
5042 days ago
5042 days ago
5042 days ago
5041 days ago
5041 days ago
5041 days ago
5041 days ago
5035 days ago
5035 days ago
2565 days ago
2565 days ago
4851 days ago
4851 days ago
4847 days ago
4847 days ago
4847 days ago
4455 days ago
4455 days ago
4455 days ago
4455 days ago
4448 days ago
4448 days ago
4447 days ago
4447 days ago
4281 days ago
4281 days ago
4281 days ago
4281 days ago
4123 days ago
4123 days ago
4084 days ago
4084 days ago
3971 days ago
3971 days ago
3915 days ago
3915 days ago
3902 days ago
3902 days ago
3861 days ago
3861 days ago
3864 days ago
3864 days ago
3861 days ago
3861 days ago
3861 days ago
3861 days ago