Hasura coversion of date time from date string

Question

I have this select query as Native query in Hasura

SELECT
    tblPhOrderSummary.Id as OrderId,
    tblPhOrderSummary.CreatedOn as OrderDate,
    tblItems.ItemCode as ItemCode,
    tblPhOrderProducts.ItemDescription,
    tblItems.ModelCode AS ModelCode,
    tblItems.Barcode AS Barcode,
    tblPhOrderProducts.LOT AS LOT,
    '' AS Batch,
    tblPhOrderProducts.Expiry AS Expiry,
    tblItems.SalesPrice AS UnitSalesPrice,
    tblPhOrderProducts.Instock AS InStock,
    tblPhOrderProducts.CurrentStock AS CurrentStock,
    tblPhOrderProducts.OrderQuantity AS OrderQuantity
FROM
    tblPhOrderProducts
INNER JOIN
    tblItems ON tblPhOrderProducts.ItemCode = tblItems.ItemCode
INNER JOIN
    tblPhOrderSummary ON tblPhOrderProducts.OrderId = tblPhOrderSummary.Id
WHERE
    tblPhOrderSummary.CustomerId = '{{CustomerId}}' 
    AND tblPhOrderSummary.CreatedOn BETWEEN '{{StartDate}}' AND '{{EndDate}}';

But when I called this from postman this returns this error

Conversion failed when converting date and/or time from character string.

But when I checked it with Azure Data Studio this works and I get my select results. But not in postman and hasura api.

Anyone know why this happens?

That looks like injection; does hasura not provide support for parameters? I hope it does. — 50 mins ago
In addition to Thom's comment, What do BETWEEN and the devil have in common? — 21 mins ago

plcplc 8648 silver badges20 bronze badges · Accepted Answer · 2023-09-19 12:41:32Z

You should not have to quote the Native Query arguments in the query text.

Hasura turns Native Query arguments into normal SQL query parameters using simple string interpolation.

That means something like

tblPhOrderSummary.CustomerId = '{{CustomerId}}'

becomes

tblPhOrderSummary.CustomerId = '@param1'

Which won’t have the desired parameter passing semantics.

If instead you were to remove the quotes, then

tblPhOrderSummary.CustomerId = {{CustomerId}}

becomes

tblPhOrderSummary.CustomerId = @param1

IIRC this should let you expose your {{CustomerId}} as a graphql integer and {{StartDate}} as a graphql date (though that will still just be string literals of course).

I know nothing about Hasura but the ODBC parameter marker is ? instead of parameter name. — 19 mins ago

Hasura coversion of date time from date string