CODEWITHSUNDEEP
sqloracle-database
donald smith
donald smith

Reputation: 25

remove time from date in Oracle SQL query

i have a query

select count(1) z1
        from sales_data
        where ed_date >= sysdate -7

i need to group by ed _date, remove the timestamp, and condition where ed_date is from 1/1/2021 to current sysdate

Upvotes: 0

Views: 1961

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

You can truncate the date:

select trunc(ed_date, 'DD') as ed_date, count(*) as z1
from sales_data
where ed_date >= date '2021-01-01'
and ed_date < trunc(sysdate, 'DD')
group by trunc(ed_date, 'DD')

The 'DD' truncation limit is the default so you could omit that, as just trunc(ed_date), if you prefer. Note though that it doesn't remove the time, it just sets it to midnight - an Oracle date always has both date and time components.

You can also order by trunc(ed_date), and in the select list you can format that however you want:

select to_char(trunc(ed_date), MM/DD/YYYY') as ed_date, count(*) as z1
from sales_data
where ed_date >= date '2021-01-01'
and ed_date < trunc(sysdate)        -- to exclude today and future
-- and ed_date < trunc(sysdate) + 1 -- to include today but not tomorrow onwards
group by trunc(ed_date)
order by trunc(ed_date)

Upvotes: 1

Related Questions