SQL union all error while using DATE_FORMATE in node-CodePudding

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'",
```

CodePudding user response：

There is no alias collection_date available, as the first subquery in the union does not select it. Use this version:

(SELECT id, DATE_FORMAT(collection_date, '%Y_%m_%d') AS dt, 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 dt;

CodePudding user response：

To produce the output with strictly the same rows ordering you must use

SELECT id, collection_date, collection_type 
FROM ( SELECT id, 
              collection_date AS collection_datetime, 
              DATE_FORMAT(collection_date, "%Y-%m-%d") AS collection_date, 
              collection_type 
       FROM bank_book 
       WHERE ledger_num = 'vr15' 
       UNION ALL 
       SELECT id, 
              collection_date, 
              DATE_FORMAT(collection_date, "%Y-%m-%d"), 
              collection_type 
       FROM cash_book 
       WHERE ledger_num = 'vr15' 
     ) AS combined_rowset
ORDER BY collection_datetime ASC