Invoke-SQLCmd Date output format

Question

I’m having a problem with a result set that I am getting from a stored procedure when running a PowerShell script (Using Invoke-SQLCmd) to get the results and send the output to a .csv file.

The date column is bringing back the time as well, see below. I have setup a simple example in Adventure works.

My Command is to execute a simple stored proc to just get the Top 10 From the Sales Order Header in Adventureworks.

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

That’s fine and I can filter out the columns I want, but I have the time 00:00:00 in the order Date Column. And I just want the date. I have googled and I am still having problems with this one. See the result set below. I need the OrderDate to just be the date. Not append 00:00:00 for the time.

SalesOrderID OrderDate 
------------ --------- 
43659 31/05/2011 00:00:00
43660 31/05/2011 00:00:00
43661 31/05/2011 00:00:00
43662 31/05/2011 00:00:00
43663 31/05/2011 00:00:00
43664 31/05/2011 00:00:00
43665 31/05/2011 00:00:00
43666 31/05/2011 00:00:00
43667 31/05/2011 00:00:00
43668 31/05/2011 00:00:00 

Answer 1

You must be invoking the query as following, or use CONVERT() as mentioned by McNets

-Q "select c1, Cast(cdate as date) as Date from TestOffline.dbo.T1"

Test Script

Create Table T1
(c1 int,
 cDate datetime)
 go

 Insert into T1 
 VALUES
 (1, '2019-11-01'),
 (2, '2019-11-02'),
 (3, '2019-11-03');

Result (sqlcmd):

P.S: Consider this post and test your case, before committing with FORMAT()

Answer 2

Invoke-SQLCmd brings back .Net DateTime object, not a string. PowerShell is responsible for changing it to string. Please check following link for more info about it: DateTime struct

You can do all the type conversions and formatting in PowerShell, there is an example:

create PROCEDURE [dbo].[SOH]
AS
    select getdate() as  OrderDate,12 as SalesOrderID

Invoke-Sqlcmd -ServerInstance "(localdb)\ProjectsV13" -Database "SSDT Demo" -Query "exec [dbo].[SOH]" |
  foreach {[PSCustomObject][Ordered]@{
    SalesOrderID = $_.SalesOrderID
    OrderDate = $_.OrderDate.ToString("MM/dd/yyyy")
    dateraw = $_.OrderDate
    }
  } | ConvertTo-Csv

and it will produce:

#TYPE System.Management.Automation.PSCustomObject
"SalesOrderID","OrderDate","dateraw"
"12","11/10/2019","11/10/2019 12:20:51 PM"

I added an additional dateraw column to better explain it, you will need to skip it for your export.
Also I added "converTo-Csv" because it was mentioned that it will be saved as a csv file.

Answer 3

If you are on SQL-Server 2012 or above you can use FORMAT function in your stored procedure.

FORMAT ( value, format [, culture ] )

DECLARE @d DATETIME = GETDATE();   
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'  

If you use an older version you can use CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT CONVERT(varchar(10), @d, 103);

| DateTime Result |
| :-------------- |
| 31/05/2011      |

| (No column name) |
| :--------------- |
| 31/05/2011       |

db<>fiddle here