Skip to Content

Displaying slashes instead of dots in dates in the query results

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]
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 18, 2017 at 11:13 PM

    Hi,

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

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

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 12, 2017 at 09:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 15, 2017 at 11:58 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jan 17, 2017 at 07:26 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19, 2017 at 05:49 AM

    Hello Guys

    Diego & Johan thanks for the help

    Nagrajans solution worked for me and it was very simple

    Thanks Nagrajan

    Regards

    Rahul

    Add comment
    10|10000 characters needed characters exceeded