Get only date without time in Oracle

Question

I have this sql request to retrieve some data and I have this columd 'pa.fromdate' which returns a date and a time. How do I get it to return only date in 'DD.MM.YYYY' format. I've tried something like trunc(to_date(pa.fromdate, 'MM.DD.YYYY')). Doesn't work. How can I do that?

SELECT pro.inscatid,   t.epotypeid,   t.paysum,   t.note,   pa.blankno,   pa.blankseria,   pa.signtime,   pa.fromdate,   ca.accnum,   ou.name,   cst.inn,   pkg_customers.GETCUSTOMERFULLNAME(cst.id) cstfio FROM epo_orders t LEFT JOIN epo_orderdetails od ON od.orderid      = t.id AND od.iscanceldoc = -1 LEFT JOIN plc_Agree pa ON pa.id = od.agreeid LEFT JOIN pro_products pro ON pro.id = pa.proid LEFT JOIN nsk_transferdetails td ON td.transferid = pa.transferid AND td.orgtypeid = 4 LEFT JOIN cst_customers cst ON cst.id = t.cstid LEFT JOIN cst_cstaccounts ca ON ca.id = t.cstaccid LEFT JOIN nsk_orgunits ou ON td.orgunitid    = ou.id WHERE t.epotypeid IN (159,1010,169,175) AND rownum         <20; 

Answer 1

Usually one would simply truncate the datetime with TRUNC:

TRUNC(pa.fromdate) 

This removes the time part from the datetime, so you get the mere date. Then in your application layer, you would care about how to display it.

For example you have a GUI with a grid. The grid displays the dates according to the user's system settings (e.g. Windows region settings), but the grid knows it's dates and can sort accordingly. For this to happen, you'd fill the grid with dates, not with strings representing a date.

If you want a fixed string format (e.g. in order to write into a file), you can use TO_CHAR instead:

TO_CHAR(pa.fromdate, 'dd.mm.yyyy')