WMUG

When creating a query that returns systems before a specific date in a collection, you may get no system records back if you had done the query like the following:

Incorrect Query
SELECT
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
FROM SMS_R_System
WHERE
SMS_R_System.CreationDate < "21/04/2011 09:05:27"

The reason why the above returns zero records is because the query criteria is telling SQL to treat the datetime as a string now. Instead, the datetime format that is specified in WQL should be used. You can find out the exact format by connecting to the SCCM site with WBEMTEST, followed by a quick "Select * From SMS_R_SYSTEM Where name = '<systemname>'", and then finding the datetime format from CreationDate. It should look like the following:

So the right query goes like this instead...

Correct Query
SELECT
SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
FROM
SMS_R_System
WHERE
SMS_R_System.CreationDate < "20110421090527.000000+***"