CODEWITHSUNDEEP
datetimeoracle10g
nirajan karki
nirajan karki

Reputation: 3

select between date range within speicific time period

I have two seperate columns for date and time each being saved in varchar2

I'm trying to query a specific range of time:

i.e. 1/1/2017 - 1/31/2017 between 6PM-6AM each day

So far I did this:

select * from (select a.*,TO_DATE(billdate||' '||billtime,'YYYY/MM/DD HH24:Mi:SS') as Timex from billtable a where billdate >= '2017/01/01' and billdate <= '2017/01/31') where timex>=to_date(''2017/01/01 18:00:00','YYYY/MM/DD HH24:Mi:SS') and timex<=to_date('2017/01/31 06:00:00','YYYY/MM/DD HH24:Mi:SS') order by billdate

What can I do further or Is It the wrong way Iam going?

Thanks!

Upvotes: 0

Views: 119

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Assuming you're stuck with the data model you have (storing dates and/or times as strings, or separately, is not a good idea) and that you are not interested in the six hours before and after the date range, the formats you've used at least allow you to query those ranges fairly simply:

select a.*, to_date(billdate||' '||billtime,'YYYY/MM/DD HH24:Mi:SS') as timex
from billtable a
where billdate >= '2017/01/01'
and billdate <= '2017/01/31'
and (billtime <= '06:00:00' or billtime >= '18:00:00')
order by billdate, billtime;

With some sample data provided in a CTE:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

with billtable (billdate, billtime) as (
  select '2017/01/01', '00:00:00' from dual
  union all select '2017/01/01', '06:00:00' from dual
  union all select '2017/01/01', '06:00:01' from dual
  union all select '2017/01/31', '17:59:59' from dual
  union all select '2017/01/31', '18:00:00' from dual
  union all select '2017/01/31', '23:59:59' from dual
)
select a.*, to_date(billdate||' '||billtime,'YYYY/MM/DD HH24:Mi:SS') as timex
from billtable a
where billdate >= '2017/01/01'
and billdate <= '2017/01/31'
and (billtime <= '06:00:00' or billtime >= '18:00:00')
order by billdate, billtime;

BILLDATE   BILLTIME TIMEX              
---------- -------- -------------------
2017/01/01 00:00:00 2017-01-01 00:00:00
2017/01/01 06:00:00 2017-01-01 06:00:00
2017/01/31 18:00:00 2017-01-31 18:00:00
2017/01/31 23:59:59 2017-01-31 23:59:59

If you already had a date, or were converting to a date - or in fact a timestamp to make this work - you could do:

select billdate, billtime, cast(timex as date)
from (
  select a.*, to_timestamp(billdate||' '||billtime,'YYYY/MM/DD HH24:Mi:SS') as timex
  from billtable a
  where billdate >= '2017/01/01' and billdate <= '2017/01/31'
)
where extract(hour from timex) < 6
or (extract(hour from timex) = 6 and extract(minute from timex) = 0 and extract(second from timex) = 0)
or extract(hour from timex) >= 18
order by timex;

Upvotes: 0

Related Questions