Need SQL query to list all packages promoted to "Prod" in the last 30 days

Document ID : KB000015989
Last Modified Date : 14/02/2018
Show Technical Document Details
Question:

What would be the best way to query for all packages promoted to the "Prod" state in the last 30 days?

Answer:

If your database is Oracle:

SELECT HARPACKAGE.PACKAGENAME,
  HARPKGHISTORY.ENVIRONMENTNAME,
  HARPKGHISTORY.STATENAME,
  HARPKGHISTORY.EXECDTIME
FROM HARPACKAGE
INNER JOIN HARPKGHISTORY ON HARPACKAGE.PACKAGEOBJID = HARPKGHISTORY.PACKAGEOBJID
WHERE HARPKGHISTORY.STATENAME = 'Prod'
AND HARPKGHISTORY.ACTION = 'Promote'
AND HARPKGHISTORY.EXECDTIME > SYSDATE - 30

If your database is SQL Server:

SELECT HARPACKAGE.PACKAGENAME,
  HARPKGHISTORY.ENVIRONMENTNAME,
  HARPKGHISTORY.STATENAME,
  HARPKGHISTORY.EXECDTIME
FROM HARPACKAGE
INNER JOIN HARPKGHISTORY ON HARPACKAGE.PACKAGEOBJID = HARPKGHISTORY.PACKAGEOBJID
WHERE HARPKGHISTORY.STATENAME = 'Prod'
AND HARPKGHISTORY.ACTION = 'Promote'
AND HARPKGHISTORY.EXECDTIME > CURRENT_TIMESTAMP - 30