Powershell - rounding up my data dates from milliseconds and truncate the milliseconds.

Question

Fellow Powershellers.

I am a SQL Dba and am creating some powershell scripts. I usually use invoke-sqlcmd without major issues.

I have come accross two issues with date precision.

1 - My stored procedures ‘absolutely’ returns a value like '2021-09-20 10:35:00.643. Invoke-sqlcmd will not. It rounds to 10:35:01. I am not able to show you the stored procedure but can mimik the scenario thru manual input in my example. To test the invok-sqlcmd you would of course need to supply your own cridential to some sql instance. The $datetest variable shows that Poweshell IS showing the full value. Is there some global setting that forces this to show the precision to milliseconds? The constraint is that I am NOT going to change the logic in the sp for dateformatting - those run fine.

param(
[string]$IN_SQLQuery = "Select convert(datetime,'2021-09-20 10:35:00.643') as BatchRunDateTime"
)

[datetime] $datetest
$datetest = '2021-09-20 10:35:00.643'
$datetest

above will show fine

$results = @(Invoke-Sqlcmd -ServerInstance $SQLServerNameConnection -Database $dbname -Username $UserName -Password $Password -Query $IN_SQLQuery -QueryTimeout 600 -AbortOnError )

foreach($result in $results)
{
$result[0]
}

You can also do the above test without the Arrary and use -Outputas datarow and altering the foreach inside; the results are the same. Something is not only rounding but ALSO stripping off the milliseconds.

2- The output from the data sample in the Invoke-SQLcmd, as well as ado, , usually rounds and drops the milliseconds - see "Here is the catch" below. Of course, if I alter the value in the stored procedure to be a string, it brings it back perfectly. So, it appears there is object intelligence that is aware of the data type coming back from the sp which is of course Datetime. It is treating the date as mm/dd/yyyy hh:mm:ss and rounding up - depending.

Here is the catch.
A If it is invoke-sqlcmd to csv, it does not round up but truncates the milliseconds.
B if it is invoke-sqlcmd to Excel cellbycell loading (slow) - it does round and still truncates the milliseconds. (corrected 9/23/2021)
C if it is invoke-sqlcmd to Excel using an array from the invoke-sqlcmd - it does round and truncates.
D if it is invoke-sqlcmd to Excel using a datarow option from the invoke-sqlcmd - it does round and truncates.
E if it is using ADO loading Excel - it does round and truncates.

I also tried formatting the Excel columns to make sure it shows mm/dd/yyyy hh:mm:ss.000. Nope, no difference.

The bottom line, I want those dates to be exactly what I have in my db. Right now, the only way I can do this is sending to csv which would not be rounded but loosing my milliseconds. For Excel it would be to change the sp to force the date to a formatted string. and I "do not" want to have to do that.

Any options I could set in the script run that can help me with this would be fantastic. Somethin like a default date format used anywhere, expecially out of invoke-sql or ado.

Many thanks
Mark

Answer 1

I think your problem might be that you're thinking of a date as a simple string and not an object with properties and methods.

If you look at one of the objects returned by the Invoke-SQL Command, what is its type? Is it "DateTime" or is it "String"?

Let's assume it's a DateTime object and that what you're seeing is the default behavior of that object when you provide no explicit format for the output.

$t = [datetime]'2021-09-20 10:35:00.643'  
$t                                          # Monday, September 20, 2021 10:35:00 AM  
$t.ToString('yyyy-MM-dd HH:mm:ss.fff')      # 2021-09-20 10:35:00.643  

If I were to display the $t variable using Format-List you'd see this:

Date : 9/20/2021 12:00:00 AM
Day : 20
DayOfWeek : Monday
DayOfYear : 263
Hour : 10
Kind : Unspecified
Millisecond : 643
Minute : 35
Month : 9
Second : 0
Ticks : 637677309006430000
TimeOfDay : 10:35:00.6430000
Year : 2021
DateTime : Monday, September 20, 2021 10:35:00 AM

You can find the string format specifiers for datetime here: custom-date-and-time-format-strings

Answer 2

Hi @Mark Gordon ,

maybe these links are helpful?

https://www.sqlservercentral.com/forums/topic/datetime-and-string-conversion-problem
https://stackoverflow.com/questions/12857716/extract-millisecond-data-from-sql-stored-procedure

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 3

I think this is just the display of the value rounding for you. The actual MS value is still there. Try this:

$results[0]["BatchRunDateTime"] | Format-List

The full value is there but when it goes to display it is using the standard HH:mm:ss format so it rounds. But the underlying value still has the correct value, including milliseconds. At least that is what I'm seeing using your code as is.

Answer 4

Hello,

Thank you for your question and reaching out.

I would suggest to use below for date formatting which should not loose milliseconds .

---

$timevariable = get-date

$MyArray = "MyVar1 = '$timevariable' "

Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "insert SomeTable (datetimecolumn) VALUES (`$(MyVar1));" -Variable $MyArray -Verbose

---

--If the reply is helpful, please Upvote and Accept as answer--