in the bellow query I am receiving the date as ‘2022-09-06T18:30:00.000Z’ I want it as YYYY_MM_DD
let sql_of_prime = `
((SELECT id , collection_date, collection_type
FROM bank_book
WHERE ledger_num = 'vr15' )
UNION ALL
(SELECT id , collection_date, collection_type
FROM cash_book
WHERE ledger_num = 'vr15' ))
ORDER BY collection_date ASC ;`
soo that’s why i used DATE_FORMAT
and now its not working
let sql_of_prime = `
((SELECT id , DATE_FORMAT(collection_date, "%Y-%m-%d") , collection_type
FROM bank_book
WHERE ledger_num = 'vr15' )
UNION ALL
(SELECT id , DATE_FORMAT(collection_date, "%Y-%m-%d") , collection_type
FROM cash_book
WHERE ledger_num = 'vr15' ))
ORDER BY collection_date ASC ;`
the first query is working but the below one is not
I have no idea why can any one suggest a solution
console log
Error: ER_BAD_FIELD_ERROR: Unknown column 'collection_date' in 'order clause'
sqlMessage: "Unknown column 'collection_date' in 'order clause'",
```
2
Answers
There is no alias
collection_date
available, as the first subquery in the union does not select it. Use this version:To produce the output with strictly the same rows ordering you must use