1

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 

3 Answers 3

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

1

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.

3
  • Again I'm executing a stored procedure, not selecting directly from the table.
    – Swiss Tony
    Nov 10, 2019 at 20:07
  • I updated the example to use a Stored Procedure matching the one you has. Nov 10, 2019 at 20:23
  • 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.
    – Swiss Tony
    Nov 11, 2019 at 10:04
1

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

2
  • 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
    Nov 10, 2019 at 17:39
  • The test is not exactly like for like. I am executing a stored procedure and trying to get a date only result.
    – Swiss Tony
    Nov 10, 2019 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.