Oracle SQL result ordering

Question

I am done the query below, which was ordered by the date of creation of the product batch. With the migration of version 11 to R12 of Oracle, the batch creation field became the migration date, that is, yesterday. As a solution, we will use the field lote, which contains the original creation date. But the order by sorts as numerical, not as date. How could SQL change for this field to be ordered as date?

SQL:

Select
    -- i.item_no Codigo,
    msi.segment1 Codigo,
  -- i.item_desc1 Descricao,
  msi.description Descricao,
    -- l.lot_no,
    mln.lot_number Lote,
  --l.sublot_no,
    mln.parent_lot_number Sublote,
 -- l.lot_desc,
    mln.description Desc_Lote,
 -- l.qc_grade,
    mln.grade_code,
 -- l.expaction_code,
    mln.expiration_action_code,
 -- l.inactive_ind,
    nvl(mln.disable_flag,'1') Ativo, 
 -- l.origination_type,
    mln.origination_type,
 -- l.expaction_date,   
  mln.expiration_action_code,
 -- l.lot_created,
  mln.creation_date Data_Criacao,
 -- l.expire_date,
    mln.expiration_date,
 -- l.retest_date,
  mln.retest_date,
  --  round(sysdate - l.lot_created) Dias,
      round(sysdate - mln.creation_date) Dias,
   -- round(sum(ili.loct_onhand)) qtde
      round(sum(moq.transaction_quantity)) qtde
From  mtl_lot_numbers       mln,
      mtl_system_items      msi,
      mtl_onhand_quantities moq,
      mtl_item_locations    mil

WHERE msi.inventory_item_id = mln.inventory_item_id
      and   msi.organization_id   = mln.organization_id
      and   msi.inventory_item_id = moq.inventory_item_id
      and   msi.organization_id   = moq.organization_id
    --  and   msi.organization_id   = 131 -- SOMENTE PA0
      and   moq.locator_id        = mil.inventory_location_id      
      and   msi.segment1 between '80000' and '99999'    
   -- and   l.lot_id  != 0 -- NAO EXISTE MAIS
   --and    i.item_no >= nvl(CodigoIni,'80000')
   --and    i.item_no <= nvl(CodigoFim,'99999')

group by        msi.segment1,
  msi.description,
    mln.lot_number,
    mln.parent_lot_number,
    mln.description,
    mln.grade_code,
    mln.expiration_action_code,
    nvl(mln.disable_flag,'1'),
    mln.origination_type,
  mln.expiration_action_code,
  mln.creation_date,
    mln.expiration_date,
  mln.retest_date,
      round(sysdate - mln.creation_date)
having  round(sum(moq.transaction_quantity)) > 0
Order by 2 asc,15 asc

Current result:

Answer 1

Quick and dirty solution:

• Reorder the positions of the intrinsic values of the LOT column (day/month/year) to a format that returns the correct order (year/month/day):

  ORDER BY SUBSTR(TO_CHAR(LOTE), 5, 4) || SUBSTR(TO_CHAR(LOTE), 3, 2) || SUBSTR(TO_CHAR(LOTE), 1, 2)

Slightly better solution:

• Convert the numeric content of the LOT field to the format YYYYMMDD; refactor your application.

Appropriate solution:

• Convert the numeric content of the LOT field to a DateTime; refactor your application.

Answer 2

order by to_date(mln.lot_number,'YYYYMMDD') , 15 ASC

but I would review the solution, better working with dates (type date).