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"

[jdhitsolutions]

At this point, I don't think I can change the DATE column from Text to anything else.

Switch -Regex ($prop.TypeNameOfValue) {
        "Int32$" { $SqlType = "Int" }
        "Int64$" { $SqlType = "Real" }
        "^System.Double$" { $SqlType = "Real" }
        "^System.DateTime" { $SqlType = "Text" }
        "^System.String$" { $SqlType = "Text" }
        "^System.Boolean$" { $SqlType = "Int" }
        default {
            $SqlType = "Blob"
        }
    } #switch

This would be a seriously breaking change, and would likely cause problems for other modules that take this module as a dependency. I'll look at updating the private function that handles the query building to convert the date time to a string.

[jdhitsolutions]

Testing with this code.

            elseif ($_.TypeNameOfValue -match "DateTime") {
                #turn DateTime into a string
                $arr.Add(@(, $_.value.ToString("yyyy-MM-dd HH:mm:ss")))
            }

[jdhitsolutions]

I have published a new version of the module to the PowerShell Gallery with DateTime fixes. There's the chance it might break modules that take this module as a dependency. But see if the latest version is more forgiving.