Query Generator/Variables

[Former Member]

In Query Generator I need to prompt for dates using [%0] and [%1]. However, I get an error once I input dates. Do the query variables come in as string format and if so, how do I compare a date input

[Former Member]

I'm not a specialist on this, but I have to learn this for the Certification exam. So, I played with Queries and noticed that the date format is driven by the Date format parameter in Administration - General settings - Display.

I tried the following query:

SELECT CardCode, UpdateDate FROM OCRD T0 WHERE T0.UpdateDate >= '[%1]' ORDER BY T0.UpdateDate

When the date format is CCYY-MM-DD it works with 2003-11-31 and 03-11-31 and 03.11.31.

When the date format is set to DD.MM.YYYY the query runs OK with 31.12.02.

HTH

Juha

[Former Member]

I checked the date settings and it is the format I have been using. Here is my query - very basic.

SELECT T1.SlpName, T0.U_Comm, T0.CardCode, T0.CardName, T0.U_EffDate, FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode WHERE T0.U_EffDate >=[%0] and <=[%1]

[Former Member]

Pls, have a look at your SQL, especially the WHERE part.

Then try this:

SELECT T1.SlpName, T0.U_Comm, T0.CardCode, T0.CardName, T0.U_EffDate, FROM OCRD T0 INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode WHERE T0.U_EffDate >=[%0] and T0.U_EffDate <=[%1]

If it doesn't work, then we should have a look on the UDF definition and/or the date(s) you enter.

HTH

Juha

[Former Member]

Hello Jacquie,

maybe this will help you, try the following syntax in your where condition:

... where T0.U_EffDate >= convert(datetime, '[%0]', 104) and T0.U_EffDate <= convert(datetime, '[%1]', 104)

also take a look at SQL Server Online documentation for further information about 'convert' and it's arguments.

kind regards

Eduard

[Former Member]

I'm not sure about "convert" being required in the B1 SQL & Formatted Search. Of course it depends on the column/UDF spex and the strings entered.

However, we should add single quotes ['] around the parameters in the Jacquie's original SQL. Just like in a System query that comes with B1:

SELECT T1.TransId, T0.DueDate, T0.LineMemo, T0.Debit, T0.Credit, T0.ExtrMatch, T0.IntrnMatch FROM JDT1 T0 INNER JOIN OJDT T1 ON T0.TransId = T1.TransId WHERE T0.ShortName = N'[%0]' AND T0.DueDate >= '[%1]' AND T0.DueDate <= '[%2]'

HTH

Juha

[Former Member]

Thank you, it was the single quotes that were missing and the fact that I had to add the UDF to the second part of the where.