skip to Main Content

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


  1. 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;
    
    Login or Signup to reply.
  2. 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
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search