At my workplace, I’ve set up an FTP server so that colleagues from across the globe can download some files that I make available for them. I noticed that two people in the same office were downloading the exact same file (10 GB in size) except one was about 10% complete while the other was about 95% complete. I wanted to find out their computer hostnames – usually just an abbreviation of their real names – so that I could let them know that they were both downloading the same file.

I always knew about the ping and nslookup commands. But none these commands would always give me the correct hostname when supplied with an ip address.

For example:
ping - a <ip_address> 
nslookup -a <ip_address>

While these commands worked for fixed ip addresses, they never seemed to work for dynamic ip addresses, and would always return a dhcp-created hostname, rather than the actual hostname of the machine.

I was delighted when I discovered a command that worked for all cases:
nbtstat -A <ip_address>

This command actually gave me a list of all the network adapters that the host contained. The Local Area Connection adapter had the actual hostname that I was looking for.

Let’s say we had the following rows in the PS_INSTITUTION_TBL:

PSUNV 01-01-1900 A
PSUNV 17-11-2010 I

If we had an Institution prompt based off of this table, we’d expect to only retrieve the most current active row. In this case it would be the first row in the table (i.e. the one with EFFDT 01-01-1900).

So let’s now take a look at the following 2 queries (both with Oracle resolved meta-SQL for %DateIn).

Incorrect Query:

Running this query would not return any rows because we’d be trying to find a row that has an effective date value of 17-11-2010 and that is active.

Correct Query:

This query will ensure that we have the most current effective dated and active date.

As a general rule, I always put the EFF_STATUS = ‘A’ condition within the effective date check. This unfortunately means that the %EffDtCheck meta-sql is essentially useless when you want to filter by EFF_STATUS (the majority of situations).


After doing some followup reading, I discovered the following link:

It seems that some would argue that no rows should be returned in my example above as the purpose of effective dating is to still maintain the history of the rows. Therefore, for a particular period (i.e until 17-11-2010), the institution was active, but right now it is currently inactive. I’ve always thought that EFF_STATUS was a soft delete toggle, but it looks as though PeopleTools does not see it that way either because when you use meta-SQL such as %EffDtCheck in my example above, no rows will be returned.

If you want to find the difference in dates between now and some date in the past/future then you can use the following query using peoplesoft meta-SQL:

The %DateDiff function returns an integer.

In Oracle you can use the subtraction operator:

Use ROUND() to round to the nearest day or TRUNC() to round down to the number of whole days.