I have the following script to list the PCs in Kace older than 3 years.

SELECT MACHINE.NAME, CS_MODEL, BIOS_SERIAL_NUMBER, DA.SHIP_DATE, USER_FULLNAME, USER_LOGGED,  MACHINE.OS_NAME
FROM MACHINE
LEFT JOIN DELL_ASSET DA on MACHINE.BIOS_SERIAL_NUMBER = DA.SERVICE_TAG

WHERE MACHINE.CS_MANUFACTURER like 'Dell%'
GROUP BY MACHINE.NAME
HAVING DA.SHIP_DATE < DATE_SUB(NOW(), INTERVAL 3 YEAR)
ORDER BY DA.SHIP_DATE, MACHINE.NAME

My question is this: how do you manipulate the 'Interval' so that, for instance, I could list PCs greater than 3 years old but less than 5? Is this possible?

Thanks!
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Yes, use date between:
DA.SHIP_DATE BETWEEN DATE_SUB(NOW(), INTERVAL 5 YEAR) and DATE_SUB(NOW(), INTERVAL 3 YEAR)

Here is a good reference:
Answered 02/14/2017 by: chucksteel
Red Belt

  • Sorry for taking so long to get back to you - I only just got the chance to test this! It works perfectly, thanks! The only thing I had to do was add HAVING at the start of the line as I got an SQL error without it. Thanks again!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share