I come from Singapore but find myself now living and working in the Netherlands, specialising in SMS 2003 / Configmgr 2007. I enjoy working and connecting people with computers and I hope I can share as much information as I can here through this blog.
“Computers do not just work like that, at the press of a button. From the moment a computer is switched on in your office, magic happens. That’s why you need an IT department and they do more than crawling under your desk to plug in that wire you kicked out.”
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