Prisma raw date query returns no result with JS date Object but has result with date string

Question

I have a raw query (postgresDb) with date input

    const orderStatArr: any = await this.prismaService
  .$queryRaw`select sum(tempt.totalAmount), count(tempt."orderId"), orders."paystat" as paystat from 
  (select sum(items.count * items.amount) as totalAmount, items."orderId" from public."orderItem" items  group by items."orderId" ) tempt
  inner join
  (select id, "paymentStatus" as paystat from public."order" 
   where "createdAt" >=  ${startDateTime} 
   AND "createdAt" <= ${endDateTime}) orders 
   on orders.id=tempt."orderId" group by paystat
 `;

startDateTime and endDateTime as passed as Date object.

Please refer console print of start datetime and enddatetime which I am using to filter the query.

The above doesn't return me any data. only an empty array.

Now if I modify the raw query with datetime string, it returns data as expected.

    const orderStatArr: any = await this.prismaService
  .$queryRaw`select sum(tempt.totalAmount), count(tempt."orderId"), orders."paystat" as paystat from 
  (select sum(items.count * items.amount) as totalAmount, items."orderId" from public."orderItem" items  group by items."orderId" ) tempt
  inner join
  (select id, "paymentStatus" as paystat from public."order" 
   where "createdAt" >=  '2023-07-13T22:09:43.528Z'
   AND "createdAt" <= '2023-07-13T22:11:54.938Z') orders 
   on orders.id=tempt."orderId" group by paystat
 `;

The above returns me result.

Now I am confused why the above query didnt' return any result but when passed it a string directly it worked. any help ...

Answer 1

You should wrap the date by single quotes. eg: '${startDateTime}'