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): enter image description here

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.

• Swiss Tony: Again I'm executing a stored procedure, not selecting directly from the table.
• Piotr Palka: I updated the example to use a Stored Procedure matching the one you has.
• Swiss Tony: Thanks Piotr, that works for me. Much appreciated! Sorry for the late reply. I needed a break :) But good to come back and see your message. Thanks again.

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

• Swiss Tony: Thanks for that, but believe it or not that does not affect the output from powershell executing the stored procedure. That still brings in the time part again.
• Swiss Tony: The test is not exactly like for like. I am executing a stored procedure and trying to get a date only result.