Get last installed windows update (date)

[AlexanderH]

Hello,
i would like to get the information of all windows server regarding the last installed windows update.
For starting i use the standard (bultin) report "Installed Windows Updates".

With this report i get some information, but the column "InstalledOn" has a format "MM/DD/YYYY" (e.g. 02/17/2017)
With this format i can't sort on the right way, because the update installed on 11/12/2015 is newer then 5/11/2018.. 😉

That's not correct.

I tried to convert the string to the right dateformat, but it doesn't work.
The best format for sorting is "YYYY/MM/DD" / "YYYY-MM-DD".

Can you help me?

Regards
Alex

[AlexanderH]

Thanks RC62N! 🙂

Your code is working and i get the result I wanted.

[RCorbeil · Accepted solution]

tblQuickFixEngineering.InstalledOn is a VarChar, not a date. Your best bet will likely be to simply slice and dice the text into a usable form.

From a skim of my tblQuickFixEngineering, I see:

• blank values
  
• M/D/YYYY
  
• MM/DD/YYYY
  
• MM/DD/YYYY 00:00:00
  
• MM/DD/YYYY hh:mm:ss
  
• %VERSION%

Try this:

CASE
  WHEN IsDate(tblQuickFixEngineering.InstalledOn) = 0
  THEN 'no date'
  ELSE SUBSTRING(tblQuickFixEngineering.InstalledOn, CHARINDEX('/', tblQuickFixEngineering.InstalledOn, 4)+1, 4) + '-' +
       RIGHT('0' + LEFT(tblQuickFixEngineering.InstalledOn, CHARINDEX('/', tblQuickFixEngineering.InstalledOn)-1), 2) + '-' +
       RIGHT('0' + SUBSTRING(tblQuickFixEngineering.InstalledOn,
                   CHARINDEX('/', tblQuickFixEngineering.InstalledOn)+1,
                   CHARINDEX('/', tblQuickFixEngineering.InstalledOn, 4) - CHARINDEX('/', tblQuickFixEngineering.InstalledOn) -1
            ), 2)
END AS InstalledOnDate

[AlexanderH]

Thanks for the reply.

In the first time i will sort the dates, but finally i would like to get the newest date from the installed date.

e.g. sorting desc: (see attachment)
Name: InstalledOn:
Server 05/10/2016
Server 03/06/2019
Server 03/03/2015
Server 02/28/2014

This sorting is not correct.

Do you unterstand my problem?


Regards
Alex

[Esben_D]

Are you sorting the report or comparing the date?

I think sorting should work. If you compare the date field to a specific value you enter, than SQL will compare two strings, which isn't intuitive for human perception.

Setting the sort type on Descending with the sort order 1 should work if you just want to sort on install date.