cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AlexanderH
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

4 REPLIES 4
AlexanderH
Engaged Sweeper II
Thanks RC62N! 🙂

Your code is working and i get the result I wanted.
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Lansweeper Employee
Lansweeper Employee
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.