CODEWITHSUNDEEP
sqlpowershelldatetimepowershell-2.0
Krishna Atmakur
Krishna Atmakur

Reputation: 55

Date field is not giving milliseconds while executing SQL script in PowerShell

I wrote a script to execute SQL query and send email to user with SQL results. Database column has Date field in format YYYY-MM-DD hh:mm:ss:mi but the SQL result in power shell giving me the format of YYYY/MM/DD hh:mm:ss. The result is not consisting of Milliseconds and date is in different format. If I have to get milliseconds also in the result date, how should I format??

param (
     [Parameter(mandatory = "true")]
     [string]$sqlserver,
     [Parameter(mandatory = "true")]
     [string]$sqlusername,
     [Parameter(mandatory = "true")]
     [string]$sqlpassword,
     [Parameter(mandatory = "true")]
     [string]$databasename
)
$query = "select * from dbo.UserDetails where createdate > '2020-04-13' order by createdate"
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$databasename'; User ID = 'sqlusername' password= '$sqlpassword'"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $query
$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $Command
$Dataset = new-object System.Data.Dataset
$DataAdapter.Fill($Dataset)
$Connection.Close()
$noOfRows = $Dataset.Tables[0].Rows.Count
$UserCreateDate = $Dataset.Tables[0].Rows[$noOfRows - 1].ItemArray[6]
Write-Host ("The Latest User created " + $UserCreateDate)

Output: 4/14/2020 12:25:03

Value in DB: 2020-04-14 12:25:03.6028312

Upvotes: 1

Views: 845

Answers (1)

Paxz
Paxz

Reputation: 3036

Powershell is converting your SQL output into a DateTime-Object. The default output format is determined by your set culture (try (Get-Culture).datetimeformat for more informations about your settings).

There are multiple ways to get the desired output:

  1. Format the Output to a string using the .ToString-Method:
$UserCreateDate = ($Dataset.Tables[0].Rows[$noOfRows - 1].ItemArray[6]).ToString("yyyy-MM-dd HH:mm:ss.fff")
  1. Using Get-Date and the -Format parameter:
$UserCreateDate = Get-Date ($Dataset.Tables[0].Rows[$noOfRows - 1].ItemArray[6]) -Format "yyyy-MM-dd HH:mm:ss.fff"

Please keep in mind that both options will convert the output into a string and won't keep the DateTime-Object.

Both solutions format the datetime with a given format-string. @Lee_Dailey was so nice to point out the possible formats from the documentation here: Custom date and time format strings

Upvotes: 1

Related Questions