CODEWITHSUNDEEP
mysqldatetimepowershell
Charlie Baum
Charlie Baum

Reputation: 107

Powershell writing to MySQL table column with datetime datatype

I have a MySQL database I am using Powershell to update data in. I can update to any text column but I'm having an issue with the 'time' columns that have a datatype of datetime. According to the MySQL docs, datetime format is YYYY-MM-DD HH:MM:SS (https://dev.mysql.com/doc/refman/5.1/en/datetime.html)

The equivalent powershell command to get date/time in that format is: get-date -format G (https://technet.microsoft.com/en-us/library/ee692801.aspx)

When I write that time to a variable and try to update a record, the record remains blank, and I'm assuming the datatype is off since I can write to integer and text columns.

Anyone have any clue how to ensure proper datatype so I can update records?

Upvotes: 0

Views: 3755

Answers (2)

Tony Hinkle
Tony Hinkle

Reputation: 4742

The previous answer was close, but the hour needs to be "HH" instead of "hh" or else you'll end up with all PM times being inserted/queried as AM.

# Gets the current datetime in correct format for a MySQL query
[string]$mySqlDateTime = get-date -Format "yyyy-MM-dd HH:mm:ss"

# Assuming you have a [datetime] variable named $datetime,
# use the following to get the correct format for a MySQL query
[string]$mySqlDateTime = $datetime.ToString("yyyy-MM-dd HH:mm:ss")

Upvotes: 2

Mark
Mark

Reputation: 588

try

get-date -Format "yyyy-MM-dd hh:mm:ss"

Upvotes: 0

Related Questions