Geek asks and answers

Oracle SQL Next_day Not Grouped

select
  next_day(zebra_time, 'mon'),
from builds
where
    zebra_time >= '01-jan-2014'
group by next_day(zebra_time, 'mon');

Zebra_time data type - DATE

Result:
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN -14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14
06-JAN-14

why arent they grouped?
Shouldn't there be just one 06-JAN-14?

+3
source share
2 answers

The type DATEcontains time, even if it is not displayed.

select
  trunc(next_day(zebra_time, 'mon'))
from builds
where
    zebra_time >= '01-jan-2014'
group by trunc(next_day(zebra_time, 'mon'));

trunc, , .

+7

zebra_time DATE, . , - NLS. , zebra_time, zebra_time, .

Try:

select
  to_char(next_day(zebra_time, 'mon'), 'MM/DD/YYYY HH12:MI:SS AM'),
from builds
where
    zebra_time >= '01-jan-2014'
group by to_char(next_day(zebra_time, 'mon'), 'MM/DD/YYYY HH12:MI:SS AM');


select
  trunc(next_day(zebra_time, 'mon')),
from builds
where
    zebra_time >= '01-jan-2014'
group by trunc(next_day(zebra_time, 'mon'));

TRUNC .

: , SO

+2

More articles:


All Articles