First and Last Day of Month

Add 2 prompts

First Prompt
Heading Type = RTF Long
Type = Character
Heading Text = Enter year (4 digit)
Format = Number Only
Length = 4
Unique Prompt Name = Bind1
Edit Type = No value

Second Prompt
Heading Type = RTF Long
Type = Character
Heading Text = Enter month (2 digit)
Format = Number Only
Length = 2
Unique Prompt Name = Bind2
Edit Type = No value

For all of the expressions you will need the settings

Type = Character
Length = 10

For the last day of the month

To use as a field enter in the text

• TO_CHAR(LAST_DAY(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD')),'YYYY-MM-DD')

To use in criteria

• TO_DATE(LAST_DAY(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD')),'YYYY-MM-DD')

For the first day of the month

To use as a field enter

• TO_CHAR(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD')

To use in criteria

• TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD')

First day of next month

To use a field enter

• TO_CHAR(ADD_MONTHS(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD'),1))

To use in criteria

• ADD_MONTHS(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD'),1)

Finally, last day of next month

To use a field enter

• TO_CHAR(LAST_DAY(ADD_MONTHS(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD'),1)),'YYYY-MM-DD')

To use in criteria

• LAST_DAY(ADD_MONTHS(TO_DATE(:1 || '-' || :2 || '-01','YYYY-MM-DD'),1))

With the ADD_MONTHS function, you can also use negative numbers to go to previous months.

As always, hope it helps.

Get the Age of an Employee

To get the age of an employee you can use MONTHS_BETWEEN. However, since MONTHS_BETWEEN requires two dates and PeopleSoft auto-magically converts date fields to a character field (TO_CHAR) when a date is used as a field in your query and the table alias is used with the field, so you will need to convert the field(s) you are using back to a date type (TO_DATE).

This expression

MONTHS_BETWEEN(SYSDATE, A.BIRTHDATE)

Will look like this in the SQL tab

MONTHS_BETWEEN(SYSDATE,TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD')

And of course when you run this, PeopleSoft Query will complain with the Oracle error message.

ORA-01861: literal does not match format string

The proper way is to do this.

MONTHS_BETWEEN(SYSDATE, TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))

Now the SQL will look like this and it will function

MONTHS_BETWEEN(SYSDATE, TO_DATE(TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

Now that you have the "MONTHS_BETWEEN" your probably saying "I don't want to know the amount of months old the employee is. I want to know the amount of years old." So, now, we will want to divide the amount of months old the employee is by 12. There are 12 months in a year. Right?

MONTHS_BETWEEN(SYSDATE, TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12

And finally if you left the data type of the expressions as a character type you are probably saying "How do I get rid of these decimals?" While you can change the expression's data type to numeric and leave the decimal value blank or change it to a zero, the FLOOR function will do this for you.

FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12)

Some other useful age calculations

Age at Hire

FLOOR(MONTHS_BETWEEN(TO_DATE(B.HIRE_DT,'YYYY-MM-DD'), TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12)

Age at Termination

FLOOR(MONTHS_BETWEEN(TO_DATE(B.TERMINATION_DT,'YYYY-MM-DD'), TO_DATE(A.BIRTHDATE,'YYYY-MM-DD'))/12)

*NOTE: If you do not use the table (record) alias as a precursor to your field, the above is not necessary. However, this is not really a good idea as another table you are using may hold the same field value. You SQL will then not know which field you are referring too. Using table aliases, while taking longer to write the expression, is always better than not using table aliases. You could write the below statement and PeopleSoft Query will not auto-magically convert the date field to a character data type.

FLOOR(MONTHS_BETWEEN(SYSDATE, BIRTHDATE)/12)

Manipulating Dates in PS Query

NOTE: This assumes your PeopleSoft installation is on an Oracle database.

When you need to manipulate a date field to be used as a field in PS Query, you must convert the date back to a date type (TO_DATE) and then to a character type (TO_CHAR). This is because PeopleSoft Query automatically converts the date to a character field when used as a field.

A date field used as a field prior to manipulation when viewing in View SQL:

• TO_CHAR(A.EFFDT,'YYYY-MM-DD')

To convert effective date to a year format, use Expressions and enter:

• TO_CHAR(TO_DATE(A.EFFDT,'YYYY-MM-DD'),'YYYY')
  

Ensure you have clicked Use as Field after saving the expression. Now view in View SQL, and you will see.

• TO_CHAR(TO_DATE(TO_CHAR(A.EFFDT,'YYYY-MM-DD'),'YYYY-MM_DD'),'YYYY-MM')

However, when manipulating a date to be used in the criteria section of your query you need only convert the date to a character (TO_CHAR).

A date field used in the criteria to manipulation when viewing in View SQL:

• A.EFFDT

To convert effective date to a year only format to be used in the critia, use Expressions and enter:

• TO_CHAR(A.EFFDT,'YYYY')
  

Now view in View SQL, and you will see.

• TO_CHAR(A.EFFDT,'YYYY')

SQL Example:

SELECT A.EMPLID, TO_CHAR(TO_DATE( TO_CHAR(A.EFFDT,'YYYY-MM-DD'),'YYYY-MM-DD'),'YYYY')
FROM PS_NAMES A
WHERE (TO_CHAR(A.EFFDT,'YYYY') = '2008')

A little crazy, I know, but that's how it is