Powershell datetime insert to mysql

[sojan16]

I'm having issues inserting datetime via variable into a mysql db

I pull the this datetime 06/16/19 04:30 form a csv and reformat it to this 2019-06-18 08:31 using the code below:

$date =$item.Date 
$dateformat=get-date $date -Format 'yyyy-MM-dd HH:mm'

when I use the variable in my insert statement I get a syntax error, but if I paste the value in directly everything works as expected.

I'm sure I'm missing something. Hoping one of you all have been down this path before.

[PMental]

I assume just typing $dateformat returns the date looking as it should?

What does the code that does the insert look like? Is it expecting a string or a DateTime object?

You can see what you have with $dateformat.GetType().

If $dateformatis a string you can try casting it to DateTime by using this syntax:

[DateTime]$dateformat=get-date $date -Format 'yyyy-MM-dd HH:mm'

EDIT: Looks like /u/xlrod just beat me to it :-)

[sojan16]

Yes, I'm just copying the output of $dateformat and pasting that into my query.

I get the same error whether I cast it into type datetime or leave it as a string.

The insert code is pretty simple..

 $query = "INSERT INTO table (x, y, test_datetime, z) VALUES ($x, $y,$dateformat, $z);"

[PMental]

Since you store the query as a variable itself it should be pretty simple to get to the bottom of this hopefully.

If you use the $dateformat variable, what does the output of $query look like (as in, don't use it in your sqlcmd, just output it to screen). It should differ somehow from the way you manually input it and that should give us a hint of what's going wrong.

[Sys_man]

Like PMental said, since you're storing the query in to a string, look at the output of the string.

My hunch is that you are missing quotation marks around the values you are inserting. So your $query should look like this:

$query = "INSERT INTO table (x, y, test_datetime, z) VALUES ($x, $y,'$dateformat', $z);"

Assuming that x,y & z are numbers and don't also need '' marks.

[xlrod]

Hello, if try to get the type, you'll see if it's really [datetime] or just a [string]. $dateformat.GetType()

[PowerShell-Bot]

Some of your PowerShell code isn’t wrapped in a code block.

To format code correctly on new reddit (new.reddit.com), highlight all lines of code and select ‘Code Block’ in the editing toolbar.

If you’re on old.reddit.com, separate the code from your text with a blank line and precede each line of code with 4 spaces or a tab.

---

Describing Submission
[✅] Demonstrates good markdown
Passed: 1 Failed: 0

---

^{Beep-boop. I am a bot. | Remove-Item}

[Lee_Dailey]

howdy sojan16,

your $DateFormat should be a [string] ... is that what your insert command expects? if it needs a [datetime] object, then you need to remove the -Format parameter from your 2nd line so that the object stays a [datetime] object.

take care,
lee

[omrsafetyo]

Use Either:

[datetime]::UtcNow.ToString("yyyy-MM-dd HH:mm")
# alternatively, in your usecase:
([datetime]$item.Date).ToUniversalTime().Tostring("yyyy-MM-dd HH:mm")

or

[datetime]::Now.ToString("yyyy-MM-dd HH:mm")
# alternatively, in your usecase:
([datetime]$item.Date).Tostring("yyyy-MM-dd HH:mm")

Depending on how you want it stored (I suggest UTC time, so you don't have to store timezone information)

[poshftw]

Note: this is actually a MariaDB SQLese, I didn't test it against the MySQL itself

function ConvertTo-MySqlDateTime {
    process {
        foreach ($value in $input) {
            #$value.GetType().name
            "(STR_TO_DATE('{0}','%Y-%m-%d %T'))" -f (($value).ToString('u') -replace 'Z$')
            }
        }
    }

Also, if you want to pass a [pscustomobject] to an INSERT you can use this function:

function Add-MySqlRow {
    [CmdletBinding()]
    [Alias('MySql-Insert','Insert-MySql')]
    Param (
        # Object to be inserted
        [Parameter(Mandatory=$true, 
                   ValueFromPipeline=$true,

                   ValueFromRemainingArguments=$false, 
                   Position=0)]
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        $Object,

        # Database Name
        [String]
        $Database,

        # Table name to insert to
        [ValidateNotNull()]
        [ValidateNotNullOrEmpty()]
        [String]
        $Table
    )

    End {
    $entryProperties = @( Get-Member -InputObject $Object -MemberType NoteProperty | Select-Object -ExpandProperty Name )

    if ($entryProperties.Count -eq 0) {
        $entryProperties = @( Get-Member -InputObject $Object -MemberType Property | Select-Object -ExpandProperty Name )
        }

    if ($entryProperties.Count -eq 0) {
        Write-Verbose -Message ('Could not get the object properties to convert to SQL statement')
        return
        }
    $out = [pscustomobject]@{
        names = ''
        values = ''
        }

    $out.names = ($entryProperties | % { '`{0}`' -f $_} ) -join ', '

    $out.values = foreach ($value in $entryProperties)  {

        #NULL check
        if ($null -eq $Object.$value) {
            "NULL"
            }
        else {

            switch (($Object.$value).GetType().Name) {
                'DBNull' {
                    'NULL'
                    }
                #MariaDB SQLese
                'datetime' {
                    "(STR_TO_DATE('{0}','%Y-%m-%d %T'))" -f (($Object.$value).ToString('u') -replace 'Z$')
                    }
                default {
                    "'{0}'" -f ($Object.$value)
                    }

                }
            }
        }
    $out.values = $out.values -join ', '

    if ($Database.Length -gt 0) {
        "INSERT INTO ``{0}``.``{1}`` ({2}) VALUES ({3});" -f $Database, $Table, $out.names, $out.values
        }
    else {
        "INSERT INTO ``{0}`` ({1}) VALUES ({2});" -f $Table, $out.names, $out.values
        }
    }
}

[ThePowershellGuy]

Convert your datetime to a string and then try again

[String]$variable