Displaying slashes instead of dots in dates in the query results

[RahF]

Hello Experts

I have a query, my problem is in the query result, SAP shows dates with dots and i need the results to display with slashes
How do i achieve it?

Thanks in advance…

SELECT 
T0.[DocNum] as 'Sales Order No', 
T0.[CardName] as 'Sales Partner', 
T0.[NumAtCard] as 'Cust Ord No', 
T1.[U_Prod_Order_No] as 'Prodn Ord No', 
T1.[ItemCode] as 'Stock Code', 
T1.[Dscription] as 'Stock Description', 
T1.[Quantity], 

--(In the below two date fields SAP date format is 12.01.2017, how do I change it to display with slashes instead of dots 12/01/2017 in the query results)
T1.[U_DelDate] as 'Lead Time', 
T1.[ShipDate] as 'Actual Delivery Date' 

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 

WHERE 
T0.[Cardcode] >= [%0] AND 
T0.[Cardcode] <= [%1] AND
T0.[DocStatus] = 'C' AND  
T0.[DocStatus]  != 'L' AND   
T1.[ShipDate]  >=[%2] AND   
T1.[ShipDate]  <=[%3] 

ORDER BY T1.[ShipDate]

[kothandaraman_nagarajan · Accepted solution]

Hi,

Use below syntax to get required date format in above query,

convert(Varchar, T1.[ShipDate] ,103)

Regards,

Nagarajan

[Johan_H]

Hi Rahul,

In that case it is going to be a little trickier. You can try a combination of the DATEPART and CAST functions.

So same query that Diego posted, but replace the FORMAT function lines with these lines:

CAST(DATEPART(dd, T1.[U_DelDate]) AS NVARCHAR) + '/' + CAST(DATEPART(MM, T1.[U_DelDate]) AS NVARCHAR) + '/' + CAST(DATEPART(yyyy, T1.[U_DelDate]) AS NVARCHAR) as 'Lead Time', 
CAST(DATEPART(dd, T1.[ShipDate]) AS NVARCHAR) + '/' + CAST(DATEPART(MM, T1.[ShipDate]) AS NVARCHAR) + '/' + CAST(DATEPART(yyyy, T1.[ShipDate]) AS NVARCHAR) as 'Actual Delivery Date' 

Please note that this will probably display a date like 01.01.2017 as 1/1/2017

Regards,

Johan

[former_member185682]

Hi Rahul,

You can achieve this with the solution proposed by Johan using Format function. Below your query with the function proposed.

SELECT 
T0.[DocNum] as 'Sales Order No', 
T0.[CardName] as 'Sales Partner', 
T0.[NumAtCard] as 'Cust Ord No', 
T1.[U_Prod_Order_No] as 'Prodn Ord No', 
T1.[ItemCode] as 'Stock Code', 
T1.[Dscription] as 'Stock Description', 
T1.[Quantity], 


--(In the below two date fields SAP date format is 12.01.2017, how do I change it to display with slashes instead of dots 12/01/2017 in the query results)
FORMAT(T1.[U_DelDate], 'dd/MM/yyyy') as 'Lead Time', 
FORMAT(T1.[ShipDate], 'dd/MM/yyyy') as 'Actual Delivery Date' 


FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 


WHERE 
T0.[Cardcode] >= [%0] AND 
T0.[Cardcode] <= [%1] AND
T0.[DocStatus] = 'C' AND  
T0.[DocStatus]  != 'L' AND   
T1.[ShipDate]  >=[%2] AND   
T1.[ShipDate]  <=[%3] 


ORDER BY T1.[ShipDate]

Kind Regards,

Diego Lother

[RahF]

Hi Diego

I am getting an error after i added the format as above

SELECT 
T0.[DocNum] as 'Sales Order No', 
T0.[CardName] as 'Sales Partner', 
T0.[NumAtCard] as 'Cust Ord No', 
T1.[U_Prod_Order_No] as 'Prodn Ord No', 
T1.[ItemCode] as 'Stock Code', 
T1.[Dscription] as 'Stock Description', 
T1.[Quantity], 
FORMAT(T1.[U_DelDate], 'dd/MM/yyyy') as 'Lead Time', 
FORMAT(T1.[ShipDate], 'dd/MM/yyyy') as 'Actual Delivery Date' 


FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 

WHERE 
T0.[Cardcode] >= [%0] AND 
T0.[Cardcode] <= [%1] AND
T0.[DocStatus] = 'C' AND  
T0.[DocStatus]  != 'L' AND   
T1.[ShipDate]  >=[%2] AND   
T1.[ShipDate]  <=[%3] 

ORDER BY T1.[ShipDate]

Please see attached error message

Thanks

Rahul17-01-2017-1-54-29-pm.jpg

[former_member185682]

Hi Rahul,

This happened because probably your sql server version is less than SQL Server 2012. The Format function is just compatible with SQL Server 2012 or higher.

Try the suggestion provided by Johan.

Kind Regards,

Diego Lother

[Johan_H]

Hi Rahul,

The B1 client should show date values in query results according to the localization settings of your client Windows installation.

If you want to format the dates in a different format, without changing Windows settings, you can try using the sql FORMAT function.

Regards,

Johan

[RahF]

Hello Guys

Diego & Johan thanks for the help

Nagrajans solution worked for me and it was very simple

Thanks Nagrajan

Regards

Rahul