Archives of the TeradataForum

Message Posted: Tue, 07 Dec 2004 @ 21:51:33 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is what we use.

     select DBC.DBASE.DATABASENAMEI (NAMED UserId),
     ((100 * ((4 * nullifzero(DBASE.PasswordChgDate) - 1) / 146097)
     + (4 * (((4 * DBASE.PasswordChgDate - 1) MOD 146097) / 4)
     + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.PasswordChgDate
     - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
     / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.PasswordChgDate - 1)
     MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
     + 1) * 100 + ((5 * (((4 * (((4 * DBASE.PasswordChgDate - 1) MOD
     146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
     (date, format 'yy/mm/dd', NAMED PasswordLastModDate),

     ((SUBSTR(CHAR2HEXINT(dbase.PasswordString),42,1)||
     SUBSTR(CHAR2HEXINT(dbase.PasswordString),44,1))*10000 +
     (SUBSTR(CHAR2HEXINT(dbase.PasswordString),48,1)||
     SUBSTR(CHAR2HEXINT(dbase.PasswordString),50,1))*100 +
     (SUBSTR(CHAR2HEXINT(dbase.PasswordString),54,1)||
     SUBSTR(CHAR2HEXINT(dbase.PasswordString),56,1)))
     (Integer, FORMAT '99:99:99', Named PasswordLastModTime),
     (CASE
     WHEN (DB2.DatabaseName = 'dbc' ) THEN 'Administrator'
     WHEN (DB2.DatabaseName = 'XXXXXX' ) THEN 'Some other way'
     ELSE DB2.DatabaseName
     END) (NAMED WhoChangedPassword)

     FROM DBC.DBASE
     LEFT OUTER JOIN DBC.Dbase DB2 ON DBC.DBase.LastAlterUID = = DB2.DatabaseID
     WHERE PasswordLastModDate IS NOT NULL

     AND PasswordLastModDate (date) > date-7
     ORDER BY PasswordLastModDate desc";

	<Prev	Next>		<<First	<Prev	Next>	Last>>