New-UDTable Column sort by date

PowerShell Universal
1

I am trying to sort on a date column, but it New-UDTable appears to be sorting it as a string. I have confirmed the field being returned from SQL is a data time.

Table Code
New-UDTable -Title ‘IT Alerts’ -Id ‘ITAlerts’ -Data $Data -Columns $Columns -PageSize 20 -Filter -Dense -Search -Export

Column Code
New-UDTableColumn -Property “NotificationDate” -Title “Notification Date” -IncludeInSearch -IncludeInExport -Sort

image
image353×505 13.1 KB

Any help would be greatly appreciated.

Cheers
Zak

Product: PowerShell Universal
Version: 1.5.9
2

Hi Zak, do you need the time part of your date? I’m sure I had this same issue in UD, so I just converted the datetime in SQL to just a short date, then everything worked in the sorting without me having to do anything fancy.

3

Unfortunately I do. Not so much for this field, but for the Last Date Edit Time field.

4

Ok, can you use some SQL skills to put the DATE in it’s own column, and then the TIME in it’s own column and try it like that?

5

Potentially, but doesnt that leave me with exactly the same issue of not being able sort by DateTime?

Also noticed its not just SQL, it does the exactly the same with a standard PS DateTime field, so very much feels like a bug to me.

(cheers for the responses by the way)

6

I’m sure if you pass it as a PSCUSTOM object the data and use something like [datetime] to force the data to be in that format…I been using UD for a while, and I am sure I did this for one of my grids passing data as a string instead of an int…sorry I don’t have an example to hand at the moment, but hopefully you get what I am saying? By all means raise this as an issue on github and I am sure this will get fixed in a future release

7

Using a CSV as a data source and creating a custom object with datetime doesnt seem to have worked (same sorting issue).

Ive logged the issue on github.

Thanks again for the help

8

@ZHumphries
i have found a fix so far that worked and show the format as you want “MM/dd/yyyy” or “dd/MM/yyyy”
let me know if you still need assistant and ill post how to apply the fix.

9

This is what I’m using as my query and I’m able to sort as date.

$SQLQuery = "Select column1, column2, convert(datetime, dateColumn, 22) as NotificationDate from Table"
 
$ExecuteQuery = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDB -Query $SQLQuery 
 
TableData = $ExecuteQuery | ForEach-Object {
          [PSCustomObject]@{
               Column1 = $_.column1
               Column2 = $_.column2
               NotificationDate = $_.NotificationDate
          }
     }

$TableColumns = @(
   New-UDTableColumn -Property “NotificationDate” -Title “Notification Date” -IncludeInSearch -IncludeInExport -Sort
)
10

Seems like others are not having the issue with sorting PowerShell date times not sorting. Would someone mind trying the following dashboard and see if it produces the same issue as I am seeing?

Cheers
Zak

New-UDDashboard -Title 'DateSortTest' -Content {
    $Dates = @(
        '14/06/2021', '14/07/2021', '14/08/2021', '14/07/2020', '20/06/2021', '20/07/2021', '20/08/2021', '20/07/2020'
    )

    $TableData = @()
    $TableData += $Dates | ForEach-Object {
        [PSCustomObject]@{
            DateSortTest = Get-Date $_
        }
    }

    $Columns = @(
        New-UDTableColumn -Property 'DateSortTest' -Title 'DateSortTest'
    )

    New-UDTable -Title 'DateSortTest' -Id 'DateSortTest' -Data $TableData -Columns $Columns -PageSize 10
}

image
image301×840 15.9 KB

11

Just thinking out loud without trying your code, but the dates need to be in yyyy-mm-dd format if you want to sort in date order

12

I guess im thinking too powershell(y) here, i.e. pass it a [datetime] object and it just deals with it as you would expect. The fact it accept a Get-Date object just reinforces that.

Ill have a play with passing it a string, but I guess I should probably look into server side sorting as doing it this way doesnt really provide a good end user experience (weirdly formatted time)

Cheers for the thoughts

13

@ZHumphries

$Dates = @(
        '14/06/2021', '14/07/2021', '14/08/2021', '14/07/2020', '20/06/2021', '20/07/2021', '20/08/2021', '20/07/2020'
    )

    $TableData = @()
    $TableData += $Dates | ForEach-Object {

    [PSCustomObject]@{
        DateSortTest = [System.DateTime]::ParseExact($_, "dd/MM/yyyy", $null)
        }
    } # | Sort-Object { $_."DateSortTest" -as [datetime] }

    $Columns = @(
        New-UDTableColumn -Property 'DateSortTest' -Title 'DateSortTest'
    )

    New-UDTable -Title 'DateSortTest' -Id 'DateSortTest' -Data $TableData -Columns $Columns -PageSize 10 -sort
14

Thanks for the attempt, unfortunately also its possible to get the in ital sort working (by removing the comment) clicking the column to sort still produces the same result.

15

Hi, I think I have the solution to your problem.

Create a class containing data for one line. It should look like this:

class MyObject {
    # A valid time string
    [string] $TimeString

    # Adds a get property to the class, allows a computed value as property instead of a function
    # https://stackoverflow.com/a/40365941
    hidden $_generateProperty= $(
        # Allows sorting of date / time
        $this | Add-Member ScriptProperty 'SortTime' `
        {
            # If time is unknown, set it to this string, will group all unknown if sorting
            # If time is known, take the filedatetime, which is comparably to: YYYYMMDDHHmm
            if([string]::IsNullOrEmpty($this.TimeString) -ne $true){
                $Time = (Get-Date -Date $this.TimeString-Format FileDateTime).ToString()
            } else {
                $Time = "unknown"
            }

            # Return computed value
            $Time
        }
    )
}

So, you define a get property, called “SortTime”, which you have to reference then as Property in the table.

New-UDTableColumn -Property SortTime -Title "DateSortTest" -Render {
    New-UDDateTime -InputObject $EventData.TimeString -Format 'HH:mm  - DD. MMMM YYYY'
}

To call the table:

New-UDTable -Title "Hi" -Id "Hi" -Columns $Columns -Data @(
    New-Object MyObject -Property @{ Time = 2020-10-10T05:34:45+00:00 }
)

Would be glad to hear if it helped you :slight_smile:

16

Powershell universal 2.0.3.

I’m having the same issue.

17

This is a regression. I’ve resolved it in the nightly build: Regression: Sorting by date in UDTable does not work. · Issue #248 · ironmansoftware/issues · GitHub

The 2.1.0 nightly for tonight will include this fix.

1 Like