SQLCMD - removing timestamp from date & formatting fields to 2 decimal places

Question

I am using SQLCMD to export out a SQL query from my server.

One of the fields I am exporting is a date field - but when it is exported it has a time stamp as well - eg: 2015-12-15 00:00:00.000

How do I go about removing the timestamp from the date such that it is: 2015-12-15

Further to this - in other exported fields that have numerical data, it is automatically exporting to 6 decimal places. How do I go about formatting the fields to 2 decimal places?

At the moment the SQLCMD is looking like this:

SQLCMD -S <server name> -d <db name> -U <username> -P <password> -Q 
"set nocount on;
SELECT 
T0.DocNum, 
T0.CardCode, 
T0.CardName, 
T0.DocDate, 
T0.DocTime, 
T0.U_FreightEntry, 
T1.ItemCode, 
T1.Dscription, 
T1.CodeBars, 
T1.Quantity, 
(((100-T0.DiscPrcnt)/100)*T1.Price) as 'Price per unit ex GST', 
((((100-T0.DiscPrcnt)/100)*T1.Price)*1.1) as 'Price per Unit Inc GST', 
(SELECT T6.Price FROM ITM1 T6 where T6.ItemCode = T5.Itemcode and T6.PriceList = '2') as 'RRP inc GST per unit', T0.VatSum, T0.DocTotal as 'Inv Total' 
FROM dbo.OINV  T0 INNER JOIN dbo.INV1  T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN dbo.OCRD  T2 ON T0.CardCode = T2.CardCode 
INNER JOIN dbo.OCRG  T3 ON T2.GroupCode = T3.GroupCode 
INNER JOIN OPLN T4 ON T2.ListNum = T4.ListNum 
INNER JOIN OITM T5 ON T1.ItemCode = T5.ItemCode 
INNER JOIN ITM1 T6 ON T5.ItemCode = T6.ItemCode and T6.Pricelist = '1'
WHERE T3.GroupName = 'NEWSXPRESS' and T0.DocDate = '20151215'
ORDER BY T0.DocNum" 
-s "," -o "<server address>\NewsxpressInvoices_%DATE:~4,2%_%DATE:~7,2%_%DATE:~-4%.csv" -h-1 -s"~" -W -w 999

Any help would be greatly appreciated.

Regards Rick

Answer 1

Search for existing answers before asking.

1) How to return the date part only from a SQL Server datetime datatype

SQlServer2008 and above:

select CONVERT(date, getdate())

2) Write a number with two decimal places SQL server

SELECT CONVERT(DECIMAL(10,2),YOURCOLUMN)