Sql-server – Invoke-SQLCmd Date output format

sql serversql-server-2016sqlcmd

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

Best Answer

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

Related Question