conversion failed when converting date and/or time from character string

[#1]

Hi experts,

I'm trying running a query in Microsoft Query but it gives the following error message:

"conversion failed when converting date and/or time from character string"

when asks me the data I'm inserting 31-01-2014

i've copy the query form the forum:

SELECT T1.CardCode, T1.CardName, T1.CreditLine, T0.RefDate, T0.Ref1 'Document Number',

     CASE  WHEN T0.TransType=13 THEN 'Invoice'

          WHEN T0.TransType=14 THEN 'Credit Note'

          WHEN T0.TransType=30 THEN 'Journal'

          WHEN T0.TransType=24 THEN 'Receipt'

          END AS 'Document Type',

     T0.DueDate, (T0.Debit- T0.Credit) 'Balance'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')<=-1),0) 'Future'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=0 and DateDiff(day, T0.DueDate,'[%1]')<=30),0) 'Current'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>30 and DateDiff(day, T0.DueDate,'[%1]')<=60),0) '31-60 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>60 and DateDiff(day, T0.DueDate,'[%1]')<=90),0) '61-90 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>90 and DateDiff(day, T0.DueDate,'[%1]')<=120),0) '91-120 Days'

     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=121),0) '121+ Days'

FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

WHERE (T0.MthDate IS NULL OR T0.MthDate > ?) AND T0.RefDate <= ? AND T1.CardType = 'C'

ORDER BY T1.CardCode, T0.DueDate, T0.Ref1

[kothandaraman_nagarajan]

Hi,

The above error appears due to date format is differnt from SAP query generator and SQL server.

So you need convert all date in above query to SQL server required format.

Try to convert..let me know if not possible.

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

Pass the date in the following format: yyyy-mm-dd. In your example: 2014-01-31.

Beni.