Using DateDiff and GetDate in WQL for SMS and ConfigMgr Queries and Collections

Another classic from my old blog (flash back to 2007) – I had to use DateDiff and GetDate in my new ConfigMgr 2012 infrastructure today, so I thought I’d re-post on my new blog…

Queries and collections based on time stamps with DateDiff and GetDate – That’s the best thing that’s happened to collections since. . .well, as long as I can remember. I use these every day, and you should too. A couple years ago, my good friend Warren Byle ran across the following bullet in the SMS 2003 SDK, in a section titled “Extended WMI Query Language”:

• In SMS 2003 the WHERE clause now supports GetDate(), DateDiff(), and DateAdd().

That’s it. . one small bullet, yet it adds so much functionality to SMS, and makes writing some of my collections much easier, and very low maintenance.

From our book, SMS 2003 Recipes: A Problem-Solution Approach, recipe 9-27 Creating a Date-Based Query:

SMS 2003 supports the following date functions in the WHERE clause of WQL queries.

GetDate(): This function will return the current date and time on the system. The data is returned in date-time format (e.g., 12:56 AM 12/02/2006).

DateDiff(): This function will return the difference between two date-time values in the increment that you specify (e.g., minute, hour, day). In our testing, the DateParts listed in Table 9-1 are supported when using DateDiff in a WHERE clause in SMS WQL.

• DateDiff ( DatePart, StartDate, EndDate )

• DatePart is the part of the date you want to calculate (e.g., minute, day, month, etc.).

• StartDate is the begin date.

• EndDate is the ending date.

DateAdd(): Returns a new date-time value based on adding an interval to the specified date. In our testing, the DateParts listed in Table 9-1 are supported when using DateAdd in a WHERE clause in SMS WQL. The proper syntax for DateAdd follows:

• DateAdd ( DatePart, Number, Date )

• DatePart is the part of the date you want to calculate (e.g., minute, day, month, etc.).

• Number is the value to increment DatePart.

• Date is a valid date-time value that will be used to calculate the new date.

Note In SMS WQL queries, never put the abbreviation in quotes—this is different than when using DateParts with date functions in VBScript.

Also mentioned in this section is GetDate(), which simply returns the current date-time from the server.

And now for some examples:

All systems that have reported a LastHardwareScan date within the last 30 days:

select *  from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) < 30

All systems that have been discovered since midnight:

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 (DateDiff(day, CreationDate, GetDate()) < 1)

Combine these queries with the power of setting the query update interval (by default is 24 hours), and you have a very dynamic query to help you with troubleshooting.  You could also combine my previous blog post on How to Create a Collection Based on Advertisement Status, using DateDiff and the LastStatusTime property from the SMS_ClientAdvertisementStatus class.