Using -like in Powershell with JSON Dates in -s (sortable) format - why am I forced to use 'American' format?

Question

I've got my dates in a JSON file in sortable (-s). The first is:

"DTSlocal": "2012-11-10T08:09:35"

This is 10th November 2012 at 08:09:35 (local)

When I load the file and convert from JSON (new object = $data), PowerShell tells me the above sample value is now:

$data[0].DTSlocal
Saturday, 10 November 2012 8:09:35 AM 

Ok, good. But, to find ALL the data from this date I've tried:

$data | Where-Object -Property DTSlocal -LIKE '10/11/2012*'   #fails
$data | Where-Object -Property DTSlocal -LIKE '11/10/2012*'   #works (!)

My Culture values are correctly set for 'Australia', viz d-MM-yyyy etc

Am I doing something wrong in having to search in 'American' format?

Update 01: Add example, as requested. Note: this is how I was trying it rather than using the subsequent suggestions. So others can learn what was going wrong. I'll also try the suggestions and report back.

Sample data file 'tinySample.json':

[
  {
    "DTSorig": "2012-11-09T21:09:35.612Z",
    "DTSlocal": "2012-11-10T08:09:35",
    "Latitude": -39.9526772,
    "Longitude": 146.8924461,
    "Source": "WIFI",
    "Accuracy": 24
  },
  {
    "DTSorig": "2012-11-09T21:11:32.285Z",
    "DTSlocal": "2012-11-10T08:11:32",
    "Latitude": -36.8526774,
    "Longitude": 142.8924487,
    "Source": "WIFI",
    "Accuracy": 23
  }
 ]

Powershell testing:

$data = Get-Content .\tinySample.json | ConvertFrom-Json

# quick look at PS object's values:
$data

DTSorig   : 9/11/2012 9:09:35 PM
DTSlocal  : 10/11/2012 8:09:35 AM
Latitude  : -39.9526772
Longitude : 146.8924461
Source    : WIFI
Accuracy  : 24

DTSorig   : 9/11/2012 9:11:32 PM
DTSlocal  : 10/11/2012 8:11:32 AM
Latitude  : -36.8526774
Longitude : 142.8924487
Source    : WIFI
Accuracy  : 23

# Find values for 10th November 2012

$data | Where-Object -Property DTSlocal -LIKE '10/11/2012*'
#nothing

$data | Where-Object -Property DTSlocal -LIKE '11/10/2012*'
# same two values returned as per  $data (above)

Answer 1

ConvertFrom-Json in PowerShell (Core) 7+ implicitly converts string JSON property values such as "2012-11-10T08:09:35" to [datetime] instances, so that is what your -like operation operates on - see this answer for more information on how ConvertFrom-Json parses dates, including how things work differently in Windows PowerShell.

• -like is a string operator, which, as all PowerShell string operators do, implicitly stringifies its LHS, if it isn't already a string.

• PowerShell typically stringifies in a culture-invariant manner; specifically, it uses the so-called invariant culture, [cultureinfo]::InvariantCulture (System.Globalization.CultureInfo.InvariantCulture), which is a culture based on the US English culture, but distinct from it and guaranteed not to change over time.

  • It is these qualities - culture-independence, long-term stability - that make the invariant culture suitable for programmatic processing, whereas culture-sensitive representations not only depend on the runtime environment but are permitted to change over time.

    • Argument-less .ToString() calls on .NET types that support culture-sensitive representations do return such culture-dependent representations, which is why you should not rely on them in programmatic processing.

  • PowerShell requests use of this invariant culture from (non-string) objects by passing [cultureinfo]::InvariantCulture, which implements the System.IFormatProvider interface, to the System.IFormatProvider-typed provider or formatProvider argument of one of the following, in order of precedence, if available (not all .NET types support culture-appropriate representations):

    • System.IFormattable.ToString, if the target object's type supports the IFormattable interface.
    • The target object's .ToString method, if it has an overload that accepts an IFormatProvider as the only parameter.

---

Since the invariant culture is based on the US English culture, it uses a month-first format in short, numeric date representations, such as when you stringify a [datetime] (System.DateTime) instance.

Applied to a simplified example:

[datetime] "2012-11-10T08:09:35" -like '11/10/2012*'

is implicitly the same as:

([System.IFormattable] [datetime] "2012-11-10T08:09:35").
  ToString(
   [NullString]::Value, 
   [System.IFormatProvider] [cultureinfo]::InvariantCulture
  )  -like '11/10/2012*'

which is implicitly the same as:

'11/10/2012 08:09:35' -like '11/10/2012*'

and therefore $true.

---

As phuclv points out, however, it's worth considering working with the properties and methods of the [datetime] (System.DateTime) type directly, instead of relying on string representations.

Answer 2

.tostring() seems to do what you want vs the implied conversion.

set-culture en-au # then restart powershell


$a = get-date


"$a"

06/12/2022 13:19:46


$a.tostring()

12/06/2022 1:19:46 PM


$a.tostring() -like '12/06/2022*'

True


set-culture en-us