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

Mark Gordon 731 Reputation points

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.

[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'

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)

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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,894 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rich Matheisen 39,176 Reputation points

    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

3 additional answers

Sort by: Most helpful
  1. Andreas Baumgarten 68,311 Reputation points MVP

    Hi @Mark Gordon ,

    maybe these links are helpful?



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

    Andreas Baumgarten

  2. Michael Taylor 41,631 Reputation points

    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.

  3. Limitless Technology 37,771 Reputation points


    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--