Invoke-SQLcmd export to csv RRS feed

  • Question

  • Hi There,

    I am exporting the results of a stored procedure to a csv file. Unfortunately when the date field arrives in the csv it is formatted as "yyyy-MM-dd HH:mm:ss" I do not want the time part, I just need it to be "yyyy-MM-dd" My code is below.

    Invoke-Sqlcmd -ServerInstance "MyServer\MyInstance" -Database "MY_Database" -Query "EXEC[MY_Database].[dbo].[My_storedproc]" | Export-Csv -NoTypeInformation  "C:\Temp\my.csv" -Force

    This is the result i get:

    "MyUser1","2019-10-16 00:00:00","14:22:00","1234567","123456789",""
    "MyUser1","2019-10-16 00:00:00","14:25:00","123123","123456789"," data, data2, Other"
    "MyUser1","2019-10-16 00:00:00","14:30:00","123123","123456789",""
    "MyUser2","2019-10-24 00:00:00","12:31:00","123456789","123456789",""
    "MyUser2","2019-10-24 00:00:00","12:34:00","123456789","123456789",""
    "MyUser2","2019-10-24 00:00:00","12:42:00","123456789","123456789",""
    "MyUser2","2019-10-24 00:00:00","14:13:00","123456789","123456789",""
    "MyUser2","2019-10-24 00:00:00","14:21:00","123456789","123456789",""
    "MyUser2","2019-10-24 00:00:00","17:04:00","123456789","123456789",""
    "MyUser2","2019-10-25 00:00:00","11:45:00","123456789","123456789",""
    "MyUser2","2019-10-30 00:00:00","09:30:00","123456789","123456789",""
    "MyUser2","2019-10-30 00:00:00","09:34:00","123456789","123456789",""

    As i said i don't want the time  00:00:00 part of the date field. any clues?

    Friday, November 8, 2019 2:15 PM

All replies

  • Thanks, but could you elaborate on that a bit. how do you calculate a date field from the example? and can this be done before it is entered into the csv? or does the csv itself have to be manipulated?
    Friday, November 8, 2019 4:18 PM
  • Because you want to export it to a text/string based output anyway you could simply cut the unwanted charachters from the string and keep the leftovers. You just have to get the first 10 charachters from the date time string. You could use the .substring() method for this. And because you want to have the result in the CSV file you will have to do this step before the export cmdlet.

    Live long and prosper!


    • Edited by BOfH-666 Friday, November 8, 2019 5:19 PM
    Friday, November 8, 2019 5:14 PM
  • Ok I have got this far with it, i can get individual columns from the result. But I am struggling to get just the date formatted as just the date and not the time.

    Invoke-Sqlcmd -ServerInstance "SOME-PC\SQL2016" -Database "AdventureWorks2016" -Query "EXEC[AdventureWorks2016].[dbo].[SOH]" | Select-Object -Property SalesOrderID,OrderDate,Status

    Still giving me all the 00:00:00 Where does the .substing() go?

           44538 01/10/2011 00:00:00      5
           44539 01/10/2011 00:00:00      5
           44540 01/10/2011 00:00:00      5
           44541 01/10/2011 00:00:00      5
           44542 01/10/2011 00:00:00      5
           44543 01/10/2011 00:00:00      5


    Sunday, November 10, 2019 2:35 PM
  • Please before you proceed read this: How to Use the Code Feature in a TechNet Forum Post.

    Then, instead of the property "OrderDate" you place a calculated property.

    Did you review the links I posted? A calculated property is basically a hashtable with 2 key - value pairs. One key is name with a name value in it and the other is an expression key with a scriptblock value in it. In this scriptblock you manipulate the property as you need. In your case I'd use as recommended already something like $_.subString(0,10).

    Live long and prosper!


    • Edited by BOfH-666 Sunday, November 10, 2019 2:54 PM
    Sunday, November 10, 2019 2:54 PM
  • Read-SqlTableData -ServerInstance "MY-PC\SQL2016" -DatabaseName "AdventureWorks2016" -SchemaName "Sales" -TableName "SalesOrderHeader" | Select-Object -Property SalesOrderID,OrderDate,Status

    Sunday, November 10, 2019 3:36 PM
  • Ok, So that code will work with and adventure works database on a computer called "MY-PC" and an instance called SQL2016. But it brings back the date as date and time.

    Sunday, November 10, 2019 3:39 PM
  • Start by reading the complete help and examples for Select-Object.

    help select-object -online

    The help shows you hos to use the command correctly and how to use a computed property.


    Sunday, November 10, 2019 5:39 PM