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

SELECT to_char(Column_name,'DD/MM/YYYY') Column_Name FROM Table_Name

Answer 2

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')

Answer 3

try this

select pro.inscatid,
t.epotypeid,
t.paysum,
t.note,
pa.blankno,
pa.blankseria,
pa.signtime,
to_char(pa.fromdate,'MM.DD.YYYY')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 4

Try to use to_char(pa.fromdate, 'MM.DD.YYYY'), and this will give you desired result

Answer 5

You can make use of to_char() function for this. And you can use trunc() to solve your issue.

Answer 6

don't use to_date() on date value column, use to_char() with mask, like this

    select 
    ......
        to_char(pa.fromdate,'MM.DD.YYYY') fromdate
    ......
from ....
where ....