List Teradata expired ID's

General
Enthusiast

List Teradata expired ID's

this program executed with no errors now that we are on V12 it's not working. getting this error 2665: Invalid date.
does anyone have another program that I can use to list Teradata id's that will expire in 1 week in the case below in the next 5 days..

SELECT DATABASENAME AS UserName,
AccountName as Accountname,
ownername,
spoolspace,
current_date,
(((100 * ((4 * PasswordChgDate - 1) / 146097) + (4 *
(((4 * DBASE.PasswordChgDate - 1) MOD 146097) / 4) + 3) /
1461 - 1900) + ((5 * (((4 * (((4 * PasswordChgDate - 1) MOD 146097) /
4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) / 12) * 10000 + (((5 *
(((4 * (((4 * PasswordChgDate - 1) MOD 146097) /
4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12 + 1) * 100 + ((5 *
(((4 * (((4 * PasswordChgDate - 1) MOD 146097) /
4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 + 5) /
5 (DATE,FORMAT 'YY/MM/DD')) + ExpirePassword AS PasswordExpiryDate,
(PasswordExpiryDate - current_date) as will_Expire_in

FROM DBC.DBASE,
DBC.SECURITYDEFAULTS
WHERE ROWTYPE = 'U' and
PasswordChgDate <> 0 and
PasswordExpiryDate is not Null and
PasswordExpiryDate between current_date and current_date +5
order by 7;
3 REPLIES
Enthusiast

Re: List Teradata expired ID's

I need this pgm or similar version as this will help our support team contact clients 1 week in advance to avoid problems with there ID's. heads up work
Junior Contributor

Re: List Teradata expired ID's

Hi Vince,
the calculation of PasswordLastModDate changed in TD12.
Have a look a the definition of dbc.users, it's much easier now :-)

CAST(PasswordModTime AS DATE) + ExpirePassword AS PasswordExpiryDate

Dieter
Enthusiast

Re: List Teradata expired ID's

thank you Dnoeth.. I made some minor changes and your response helped me with my results.. happy holidays.. ciao