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.ClientFROM SMS_R_System WHERESMS_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 SELECTSMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.ClientFROMSMS_R_System WHERESMS_R_System.CreationDate < "20110421090527.000000+***"
UTC format, i dug out this super cool converter which explains what it means
Powered by Zimbra