I've been asked to add the most recent day that a patch was installed on a
collection of machines. I figured it would be fairly easy to accomplish with
the data in the Quick Fix Engineering table, but the InstalledOn date is
actually in a nvarchar format, so I can't use the max() function to figure it
out. Seems that some of the dates are Windows datetime strings, some of them
are just plain empty.
I played around with converting the data on the fly, and I've gotten close but
not far enough. Here is what I've come up with so far (check to see if the
nvarchar data is a date, convert it to a date time object if true):
SELECT TOP (100) PERCENT dbo.v_R_System.Name0, CASE
(ISDate(dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0)) WHEN '1' THEN
CONVERT(date,
dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0, 101) ELSE
NULL END AS Date
FROM dbo.v_GS_QUICK_FIX_ENGINEERING LEFT OUTER JOIN
dbo.v_R_System ON
dbo.v_GS_QUICK_FIX_ENGINEERING.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_FullCollectionMembership AS fcm ON
fcm.ResourceID = dbo.v_R_System.ResourceID
WHERE (fcm.CollectionID = @COLLID)
GROUP BY dbo.v_R_System.Name0, dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0
ORDER BY dbo.v_R_System.Name0
Problem starts when I try to add the max function, it just errors out. Anyone
else out there done something similar and get it to work?
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
