CODEWITHSUNDEEP
sqloracle-database
JDoeBloke
JDoeBloke

Reputation: 591

Get only date without time in Oracle

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;

Upvotes: 37

Views: 184761

Answers (7)

Ashish Gupta
Ashish Gupta

Reputation: 191

To get data for only date filter, use TRUNC in front of column name.

Like use TRUNC(table.column_name) = 'DD-MM-YYYY'

Example: TRUNC(Employee.Joining_Time)= '01-JAN-2021'

Just use same format of date as per in the column used where you have full string of date concat time.

Thanks.

Upvotes: -2

Dean Newstead
Dean Newstead

Reputation: 35

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

Upvotes: 1

Vecchiasignora
Vecchiasignora

Reputation: 1315

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 ....

Upvotes: 4

Sindhu
Sindhu

Reputation: 418

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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

Upvotes: 73

Akanksha
Akanksha

Reputation: 101

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

Upvotes: 5

CompEng
CompEng

Reputation: 7376

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

Upvotes: 1

Related Questions