How to use momentJS with nodejs and mysql custom query-CodePudding

I want to implement the following sql query:

date_count = await customQuery(
    `
        SELECT 
            COUNT(org.name 
                OR c.name 
                OR co.name 
                OR seg.arrival_details 
                OR seg.departure_details 
                OR seg.segment_airline_id 
                OR seg.segment_arrival_airport_code
                OR seg.segment_departure_airport_code 
                OR seg.segment_flight_number 
                OR seg.trip_id 
                OR b.trip_id
                OR bf.pnr_no 
                OR bf.airline_pnr_no 
                OR b.booking_status 
                OR b.created 
                OR org.organisation_id 
                OR org.name
            ) AS total_count
        FROM 
            segment_details AS seg
            LEFT JOIN trip_details AS tr ON seg.trip_id = tr.trip_id
            LEFT JOIN bookings_flight AS bf ON tr.bookings_flight_id = bf.booking_flight_id
            LEFT JOIN bookings AS b ON bf.booking_id = b.booking_id
            LEFT JOIN organisation AS org ON b.organisation_id = org.organisation_id
            LEFT JOIN city AS c ON org.city_id = c.city_id
            LEFT JOIN country AS co ON org.country_id = co.country_id
        WHERE 
            seg.segment_departure_date BETWEEN 
                ${moment(date.startDate).startOf('day').format("YYYY-MM-DD HH:mm:ss") }
                AND ${moment(date.endDate).endOf('day').format("YYYY-MM-DD HH:mm:ss")}  
    `)

But I am getting the following error:

UnhandledPromiseRejectionWarning: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00:00

my date result comes as : '2021-10-01T08:00:00.000Z'

and when I apply moment js to it my result is as follow: '2021-10-01 00:00:00'

I am using MySQL, SQL Yog

How can I fix this error? Any help or suggestion will be appreciated.

CodePudding user response：

Try to surround your dates with single quotes:

...
seg.segment_departure_date BETWEEN 
    '${moment(date.startDate).startOf('day').format("YYYY-MM-DD HH:mm:ss")}'
    AND '${moment(date.endDate).endOf('day').format("YYYY-MM-DD HH:mm:ss")}'

CodePudding user response：

Try it out. You need to add single quotes

const startDate = moment(date.startDate).startOf('day').format("YYYY-MM-DD HH:mm:ss");
const endDate = moment(date.endDate).endOf('day').format("YYYY-MM-DD HH:mm:ss");

...

WHERE 
   seg.segment_departure_date BETWEEN '${startDate}' AND '${endDate}'