How to convert datetime from PowerShell output to yyyy-MM-dd HH:mm:ss format

user_15738143 0
Viewed 425

While trying to write a lastLogon date to DB from PS I am getting the output 10-10-2021 21:14:40 but when writing this to my DB (MS SQL) the date converts 1900-01-01 00:00:00.000 instead of 2021-10-10 21:14:40.000 and any attempts to convert the output using e.g. $DateStr = $lastLogon.ToString("yyyy-MM-dd HH:mm:ss.sss") has failed so far

My PS Script:

.....
Import-Module ActiveDirectory
Add-Type -AssemblyName System.Web

$EmploymentUser = Invoke-SQL -sqlCommand "select * from EmploymentUser where LastFlowEditDate Is Not NUll AND (EndDate Is Null OR EndDate > CURRENT_TIMESTAMP) AND StartDate < CURRENT_TIMESTAMP ORDER BY Initials;"

foreach($User in $EFP_EmploymentUser)
{
Get-ADUser $User.Initials -Properties lastLogon | Select @{Name="lastLogon";Expression={[datetime]::FromFileTime($_.'lastLogon')}}

    Invoke-SQL -sqlCommand "UPDATE EFP_EmploymentUser SET lastLogonAD  = '$($lastLogon)' WHERE ID like $($User.ID)"

}

Any one who can help?

powershell-4.0 powershell sql-server
1 Answers

Use a date-time string in ISO 8601 format - e.g. 2021-10-10T21:14:40 - which you can obtain with the standard s format specifier:

foreach ($user in $EFP_EmploymentUser) {

  $lastlogon = [datetime]::FromFileTime(
    (Get-ADUser $User.Initials -Properties lastLogon).lastlogon
  )

  Invoke-SQL -sqlCommand "
      UPDATE EFP_EmploymentUser
      SET lastLogonAD  = '$($lastLogon.ToString('s'))'
      WHERE ID like $($user.ID)
    "

}

The advantage of using a ISO 8601-format string is that it is by definition culture-invariant.

A simplified example, using Get-Date:

PS> (Get-Date).ToString('s')
2021-10-10T17:42:41

Note: If Get-Date happens to be involved anyway, you can more simply use its -Format parameter:

PS> Get-Date -Format s
2021-10-10T17:42:41

As for what you tried:

The primary problem with your code was that you tried to use an undefined variable, $lastlogon, instead of accessing the .lastlogon property on the current pipeline input object, $_, as created by your Select-Object call.

In the context of string interpolation (inside a "..." string), the undefined $lastlogon variable expanded to the empty string, which is why the SQL command ended up assigning the earliest date-time value it supports, 1900-01-01 00:00:00.000

Additionally, your Get-ADUser ... statement was not connected to the Invoke-SQL call, so that the former's output - a [pscustomobject] instance with a calculated .lastlogon property created via Select-Object - was simply passed through.

user_453750
Related