[Bug]: Issue when getting PSWorkItems likely culture-related to datetime

[Tuttu]

Describe the problem

I'm running a Windows 10 in French so the date time format is also in French. After adding a PSWorkItem, it is sometimes impossible to retreive it and the following error is displayed instead.

❯ $ New-PSWorkItem -Name 'Remove McAffee and Sophos AV' -Category Application -DaysDue 30
❯ $ Get-PSWorkItem
InvalidArgument: C:\Users\MichaëlDeBona\Documents\PowerShell\Modules\PSWorkItem\0.6.0\functions\public\Get-PSWorkItem.ps1:94
Line |
  94 |              $tasks = ($tasks).Where({[datetime]$_.duedate -le $d})
     |                                       ~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot convert value "16/10/2022 10:07:04" to type "System.DateTime". Error: "String '16/10/2022
     | 10:07:04' was not recognized as a valid DateTime."

WARNING: Failed to find any matching tasks

Howerver, this does work sometimes. Example with this item. The date format is the same but the item is returned correctly.

ID Name                                              Description DueDate             Category Pct
-- ----                                              ----------- -------             -------- ---
 5 Plan meeting to show DaaS to team                             03/08/2022 14:52:52 Intune     0

Whether the due date is in the past or the future does not seem to matter as work items that are set both in the past or in the future either fails to be fetched or are displayed correctly.

For the record, the items have been imported from your MyTask module using the following command: Get-Mytask | % { New-PSWorkItem -Name $_.Name -Category $_.Category -DueDate (Get-Date $_.Duedate) }

Expectation

Whatever the due date set on a PSWorkItem, it should be returned correctly independently to the date time format.

Additional Information

As stated in the title and above, I'm on a French computer. The date format is the following:

❯ $ Get-Date
vendredi 16 septembre 2022 10:19:28

If needed, I can send you a copy of the database file.

PowerShell version

7.2

Platform

Windows 10 Pro or Enterprise

Additional Checks

• [X] You are using the latest version of this module.
• [X] You have read this repository's README file.
• [X] You have read full help and examples for the command you are having problems with.
• [X] You are running PowerShell in an elevated session.
• [X] You are running in a traditional PowerShell console or Windows Terminal

[jdhitsolutions]

International date handing is always tricky. Let me see what I can do.

[Tuttu]

For some reason, it is working correctly with the MyTask module. Are you handling dates differently? If needed, I can switch to ISO date format (yyyy-MM-dd). Would that help?

[jdhitsolutions]

It could be related to the sqlite database. The datetime is stored as a string. When the psworkitem is created the string is treated as a [datetime] object. I may have to adjust how the string is parsed.

[Tuttu]

Got it. If needed, I'm available for testing if you don't want to bother with using a French (Or any other language) computer. :)

[jdhitsolutions]

I think I have this figured out. I keep forgetting that PowerShell doesn't respect culture when using expansion.

I was using Get-Date subexpressions in the SQL query which resulted in a culture mismatch.

[Tuttu]

Awesome! Good to read. :)

[jdhitsolutions]

Cleaning up some other bugs I came across as well.

[jdhitsolutions]

Update the module to v0.7.0 and try again. You might need to run Set-PSWorkItem on tasks to properly update the database. Worse case, remove the tasks with "bad" dates and re-enter them.

[jdhitsolutions]

Or, use the SQLite command to export the database to a json file.

Export-MySQLiteDB -path $PSWorkItemPath -destination dbexport.json

Edit the dates in the JSON file. Then recreate the database.

import-mySQLiteDB -Path .\dbexport.json -Destination $PSWorkItemPathgwio

[Tuttu]

Hello @jdhitsolutions,

Sorry for the late reply. I updated the module to version 0.7 and it fixed the problem without even needing to import the work items again. It looks like they are now working like a charm. 🙂

[x-yap]

I got a similar problem. I just started using your module and created a task with New-PSWorkItem 'DB Security check' -Category 'work' When calling Get-PSWorkItem it doesn't return anything, but Get-PSWorkItem -All did infact print the task, but also some errors with the date conversion.

I have to current module version (1.11.0) I'm running windows with regional format set to English (Germany)

PS C:\> Get-PSWorkItem
PS C:\> Get-PSWorkItem -all
SetValueInvocationException: C:\Users\user\Documents\PowerShell\Modules\PSWorkItem\1.11.0\functions\private\helpers.ps1:29
Line |
  29 |      $item.DueDate = $data.DueDate #-as [DateTime]
     |      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "DueDate": "Cannot convert value "29/03/2025 15:48:26" to type "System.DateTime". Error:
     | "String '29/03/2025 15:48:26' was not recognized as a valid DateTime.""
SetValueInvocationException: C:\Users\user\Documents\PowerShell\Modules\PSWorkItem\1.11.0\functions\private\helpers.ps1:30
Line |
  30 |      $item.TaskCreated = $data.TaskCreated #-as [DateTime]
     |      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "TaskCreated": "Cannot convert value "27/02/2025 15:48:26" to type "System.DateTime". Error:
     | "String '27/02/2025 15:48:26' was not recognized as a valid DateTime.""
SetValueInvocationException: C:\Users\user\Documents\PowerShell\Modules\PSWorkItem\1.11.0\functions\private\helpers.ps1:31
Line |
  31 |      $item.TaskModified = $data.TaskModified #-as [DateTime]
     |      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception setting "TaskModified": "Cannot convert value "27/02/2025 15:48:26" to type "System.DateTime". Error:
     | "String '27/02/2025 15:48:26' was not recognized as a valid DateTime.""

   Database: C:\Users\user\PSWorkItem.db

ID Name                Description DueDate             Category Pct
-- ----                ----------- -------             -------- ---
 1 DB Security check             29/03/2025 16:01:26 work 0

PS C:\> [system.datetime]::Parse("27/02/2025 15:48:26")

Thursday, 27 February 2025 15:48:26

PS C:\> [datetime]::Parse((Get-PSWorkItemData).taskcreated)

Thursday, 27 February 2025 15:48:26

[jdhitsolutions]

Cultures and datetime formats are a pain. Can you run get-psworkitem -verbose and paste the verbose output? And also run New-PSWorkItem 'Foo' -Category 'work' -verbose -whatIf and paste that output. This will give me a better idea on how your computer and the module are handling dates.

[x-yap]

Ok that I didn't get any output for Get-PSWorkItem seems intended as my task isn't due in the next 10 days.

PS C:\> Get-PSWorkItem -Verbose
VERBOSE: [22:09:50:3718 BEGIN  ] Get-PSWorkItem->  Running under PowerShell version 7.5.0
VERBOSE: [22:09:50:3725 BEGIN  ] Get-PSWorkItem->  Using PowerShell Host ConsoleHost
VERBOSE: [22:09:50:3733 BEGIN  ] Get-PSWorkItem->  Running under Operating System Microsoft Windows 10.0.22631
VERBOSE: [22:09:50:3740 BEGIN  ] Get-PSWorkItem->  Using module PSWorkItem version 1.11.0
VERBOSE: [22:09:50:3746 BEGIN  ] Get-PSWorkItem->  Using SQLite database C:\Users\user\PSWorkItem.db
VERBOSE: [22:09:50:3750 BEGIN  ] Get-PSWorkItem->  Detected culture en-DE
VERBOSE: [22:09:50:3755 PROCESS] Get-PSWorkItem->  Detected parameter set days
VERBOSE: [22:09:50:3759 PROCESS] Get-PSWorkItem->  Select * from tasks
VERBOSE: [22:09:50:3804 PROCESS] Get-PSWorkItem->  Found 1 matching PSWorkItem tasks
VERBOSE: [22:09:50:3809 PROCESS] Get-PSWorkItem->  Re-filtering for PSWorkItems due in the next 10 day(s)
VERBOSE: [22:09:50:3811 PROCESS] Get-PSWorkItem->  Cutoff date is 09/03/2025 22:09:50
VERBOSE: [22:09:50:3814 PROCESS] Get-PSWorkItem->  Filtering for PSWorkItems due before 09/03/2025 22:09:50
VERBOSE: [22:09:50:3818 PROCESS] Get-PSWorkItem->  Re-Filtering found 0 PSWorkItems
VERBOSE: [22:09:50:3821 PROCESS] Get-PSWorkItem->  Sorting 0 items
VERBOSE: [22:09:50:3825 END    ] Get-PSWorkItem->  Ending module command
VERBOSE: [22:09:50:3828 END    ] Get-PSWorkItem->  Command completed in 00:00:00.0110376

PS C:\> New-PSWorkItem 'Foo' -Category 'work' -Verbose -WhatIf
VERBOSE: [22:09:51:8676 BEGIN  ] New-PSWorkItem->  Starting module command
VERBOSE: [22:09:51:8684 BEGIN  ] New-PSWorkItem->  Running under PowerShell version 7.5.0
VERBOSE: [22:09:51:8692 BEGIN  ] New-PSWorkItem->  Using PowerShell Host ConsoleHost
VERBOSE: [22:09:51:8698 BEGIN  ] New-PSWorkItem->  Running under Operating System Microsoft Windows 10.0.22631
VERBOSE: [22:09:51:8703 BEGIN  ] New-PSWorkItem->  Using module PSWorkItem version 1.11.0
VERBOSE: [22:09:51:8707 BEGIN  ] New-PSWorkItem->  Using SQLite database C:\Users\user\PSWorkItem.db
VERBOSE: [22:09:51:8711 BEGIN  ] New-PSWorkItem->  Detected culture en-DE
VERBOSE: [22:09:51:8832 PROCESS] New-PSWorkItem->  Validating PSWorkItem category work
VERBOSE: [22:09:51:8849 PROCESS] New-PSWorkItem->  Adding PSWorkItem Foo with category work due 29/03/2025 22:09:51
VERBOSE: [22:09:51:8920 PROCESS] New-PSWorkItem->  PSWorkItem 27/02/2025 22:09:51 created
VERBOSE: [22:09:51:8927 PROCESS] New-PSWorkItem->  Insert into tasks (taskid,taskcreated,taskmodified,name,description,category,duedate,progress,completed,id) values ('307f1c5e-236c-493f-a9dd-213bdf3f4739', '27/02/2025 22:09:51','27/02/2025 22:09:51','Foo','','work', '29/03/2025 22:09:51', '0','False','2')
What if: Performing the operation "Create PSWorkItem" on target "Foo [] Category: work Due: 29/03/2025 22:09:51".
VERBOSE: [22:09:51:8940 END    ] New-PSWorkItem->  Closing the SQLite database connection
VERBOSE: [22:09:51:8948 END    ] New-PSWorkItem->  Ending module command
VERBOSE: [22:09:51:8953 END    ] New-PSWorkItem->  Command completed in 00:00:00.0277181

[jdhitsolutions]

I have an idea that might address this. I'll need a little time to revise the module and test.

[jdhitsolutions]

I've published v1.12.0 to the PowerShell Gallery. See if this handles the date time culture problems.