Hasura coversion of date time from date string

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?

Solution

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 = '?'

Which won't have the desired parameter passing semantics.

If instead you were to remove the quotes, then

tblPhOrderSummary.CustomerId = {{CustomerId}}

becomes

tblPhOrderSummary.CustomerId = ?

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).

A good way to diagnose this is to us the Analyze button on a query that uses the native query in the hasura console. That should tell you if this is what you're suffering from.