1

Topic: to_date Without date and comparing on time

Time is stored in table 2 of a field with type date in which: start_time, end_time
It is necessary to receive a line, at which an interval between start_time and end_time  sysdate
Application by default sits down date of 1/1/1980.  that it always will such I not I can (who knows that there exchanges).
My first decision looks as follows:

with t as (
select to_date (' 1/1/1980 OF 9:00:00 AM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 9:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 0:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 10:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 6:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 11:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual
)
select * from t
where
to_char (sysdate, ' hh24:mi:ss')
between
to_char (start_time, ' hh24:mi:ss')
and
to_char (end_time, ' hh24:mi:ss');

But after all this string comparison, instead of dates. Why that seemed to me that it is not true and consequently the second variant looks already here so

with t as (
select to_date (' 1/1/1980 OF 9:00:00 AM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 9:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 0:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 10:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 6:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 11:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual
)
select * from t
where
to_date (to_char (sysdate, ' hh24:mi:ss'),'hh24:mi:ss')
between
to_date (to_char (start_time, ' hh24:mi:ss'),'hh24:mi:ss')
and
to_date (to_char (end_time, ' hh24:mi:ss'),'hh24:mi:ss');

And from here the second question - why  follows when do

SQL> select to_date (' 10:00:00 AM ', ' hh24:mi:ss') from dual;
TO_DATE (' 10:00:00 AM ', ' HH24:MI:SS ')
--------------------------------
10/1/2017 OF 10:00:00 AM

selects date of equal 1 number, current month. Whether it is possible on it . In dock of the answer did not find. Stick if who finds pozh.
Well it is already side question smile
Misters, your judgement would be desirable to hear. What of approaches is more adequate or as though you solved this task.
Thanks

2

Re: to_date Without date and comparing on time

Kido;
In what a problem with string comparison?
I would remove separators
https://docs.oracle.com/cd/B28359_01/se … SQLRF51049

wrote:

If you specify a date value without a date, then the default date is the first day of the current month.

.....
stax

3

Re: to_date Without date and comparing on time

with t as (
select to_date (' 1/1/1980 OF 9:00:00 AM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 9:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 0:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 10:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 6:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 11:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual
)
select sysdate, t.*
from t
where numtodsinterval (sysdate - trunc (sysdate), ' day ') between
numtodsinterval (start_time - trunc (start_time), ' day ')
and numtodsinterval (end_time - trunc (end_time), ' day ');
SYSDATE START_TIME END_TIME
------------------- ------------------- -------------------
2017-10-06 0:50:14 PM 1980-01-01 9:00:00 AM 1980-01-01 3:00:00 PM
2017-10-06 0:50:14 PM 1980-01-01 0:00:00 PM 1980-01-01 10:00:00 PM

4

Re: to_date Without date and comparing on time

AmKad;

where sysdate - trunc (sysdate) between start_time - trunc (start_time) and end_time - trunc (end_time)

5

Re: to_date Without date and comparing on time

AmKad, Stax
It is more than thanks!

6

Re: to_date Without date and comparing on time

Kido wrote:

Raschityvat that it always will such I not I can (who knows, that there exchanges ).

will be if start and  will be not in one day
in midnight (the third change)
smile))
.....
stax

7

Re: to_date Without date and comparing on time

Kido wrote:

Application by default sits down date of 1/1/1980

Kido wrote:

or as though you solved this task.

To tear off from the author of application the penalty for  date and to demand finishing under the adequate data.

8

Re: to_date Without date and comparing on time

Stax wrote:

it is passed...
will be if start and  will be not in one day
in midnight (the third change)
smile))
.....
stax

with t as (
select to_date (' 1/1/1980 OF 9:00:00 AM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 3:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 9:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 0:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 10:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual union all
select to_date (' 1/1/1980 OF 6:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 1/1/1980 OF 11:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual
)
select sysdate, t.* from t where to_date (to_char (t.start_time, ' dd.mm.yyyy ') || ' ' || to_char (sysdate, ' hh24:mi:ss'), ' dd.mm.yyyy hh24:mi:ss') between t.start_time and t.end_time

9

Re: to_date Without date and comparing on time

Fogel;
On directors depends as well as what to consider

with t as (
select to_date (' 9/6/2017 OF 11:00:00 PM ', ' dd.mm.yyyy hh24:mi:ss') as start_time, to_date (' 9/7/2017 OF 7:00:00 AM ', ' dd.mm.yyyy hh24:mi:ss') as end_time from dual
)
select sysdat, t.* from t;
(select to_date (' 10/6/2017 OF 1:00:00 AM ', ' dd.mm.yyyy hh24:mi:ss ') sysdat from dual) - sysdate
where to_date (to_char (t.start_time, ' dd.mm.yyyy ') || ' ' || to_char (sysdat, ' hh24:mi:ss'), ' dd.mm.yyyy hh24:mi:ss') between t.start_time and t.end_time
/

......
stax

10

Re: to_date Without date and comparing on time

env;
The author of application himself tears off from us considerable money. In the opposite direction this mechanism does not work smile