DateTime object parsing

[DanGough]

The following code works for me:

$Date = Get-Date '01/01/2000'

$StateEntry = [PSCustomObject]@{
    ID = (New-Guid).Guid
    Date = $Date
}

$DbPath = "$env:TEMP\state.db"

[System.GC]::Collect()

if (Test-Path -Path $DbPath) {
    Remove-Item -Path $DbPath -Force -ErrorAction SilentlyContinue
}

$StateEntry | ConvertTo-MySQLiteDB -Path $DbPath -TableName State -TypeName State -Primary ID -Force

[System.GC]::Collect()

ConvertFrom-MySQLiteDB -Path $DbPath -TableName State -PropertyTable propertymap_State

However this does not work in the UK when the date is set to 29/02/2024; the raw table text contains the date in MM/DD/YYYY format, which fails to parse back to DateTime.

This is because of how PowerShell converts DateTime to String. When you use .ToString() it uses the current culture settings. When using string interpolation, it uses the default invariant culture.

> (Get-Date).ToString()
29/02/2024 00:00:00

> "$(Get-Date)"
02/29/2024 00:00:00

To fix this, you'd either need to store the strings in the local culture format (by calling .ToString() on them), or decode them differently:

$dateString = '29/02/2024 00:00:00'
$format = 'dd/MM/yyyy HH:mm:ss'
$culture = [System.Globalization.CultureInfo]::InvariantCulture

$date = [DateTime]::ParseExact($dateString, $format, $culture)

[jdhitsolutions]

Well, that's annoying and one of those little things that you'd never consider.

[DanGough]

BTW I tried this alternative module out - the readme has an example that sets the type in the base table as DATETIME rather than TEXT, and it ends up converting to and fro as expected:

https://github.com/RamblingCookieMonster/PSSQLite

[jdhitsolutions]

It's been a while, but I think I started down the DateTime path but switched to Text to avoid culture-related issues, but apparently, it didn't do me any good.

[B-Art]

The normal way to approach this, is to make a distinction between saving and displaying data. MM/DD/YYYY or DD/MM/YYYY is purly for Display. It shouldn't be used for saving a date. For saving only use YYYY/MM/DD (from big to small as any other number). Input for a database should always be interpreted first before saving it. LTE (Load, Transform and Enrich). Get-Date has a nice formatting for this. Get-Date -format "yyyy\/MM\/dd"