
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-06-2019 02:47 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 05:13 PM
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:
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%
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
4 REPLIES 4

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-18-2019 04:44 PM
Thanks RC62N! 🙂
Your code is working and i get the result I wanted.
Your code is working and i get the result I wanted.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 05:13 PM
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:
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%
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-08-2019 09:07 AM
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
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-07-2019 05:24 PM
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.
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.
