Display Time [message #673641] |
Wed, 28 November 2018 03:40 |
|
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
select * from INV
Where INV_TIME between '12:00:29 AM' and '07:00:03 PM'
no rows selected
Above query is not displaying result.
Create table INV(
INV_ID VARCHAR2(5),
INV_DATE DATE,
INV_TIME VARCHAR2(14))
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00001','22-NOV-2018','12:00:29 AM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00002','22-NOV-2018','12:00:35 AM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00003','22-NOV-2018','12:00:38 AM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00004','22-NOV-2018','12:03:03 AM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00005','22-NOV-2018','12:04:03 AM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00006','22-NOV-2018','06:30:03 PM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00007','22-NOV-2018','06:45:03 PM')
/
insert into INV (INV_ID,INV_DATE,INV_TIME) Values ('00008','22-NOV-2018','07:00:03 PM')
select * from INV
Where INV_TIME between '12:00:29 AM' and '07:00:03 PM'
no rows selected
|
|
|
|
|
|
|
|
Re: Display Time [message #673649 is a reply to message #673645] |
Wed, 28 November 2018 06:19 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The reason the original query returns no rows is because the rules for comparing strings are different to the rules for comparing date times.
inv_time is a string. '12:00:29 AM' and '07:00:03 PM' are strings.
String comparisons compare each character in turn until it finds one that's different - basic alphabetical ordering (but with numbers and symbols added).
So any string that starts with 1 will come after any string that starts with 0:
SQL> SELECT GREATEST('12:00:29 AM', '07:00:03 PM') FROM dual;
GREATEST('12:00:29AM','07:00:03PM')
-----------------------------------
12:00:29 AM
SQL>
The time 07:00:03PM comes after the time 12:00:29 AM but oracle doesn't know it's supposed to be comparing times since you didn't tell it. Which is why the others are telling you to use to_date.
I'm assuming the inv_time goes with the inv_date (ie time is on the day specified by date).
In which case you need to get rid of the time column.
Oracle dates store times to the second. Having the time in a seperate column just makes all date/time comparisons a pain (more code, less efficient, more bug prone, harder to maintain).
Say you want all rows after 12pm on the 1st of November 2018.
With your table like that you would need to write:
SELECT * FROM inv
WHERE inv_date > to_Date('02-NOV-2018', 'DD-MON-YYYY')
OR (inv_date = to_Date('01-NOV-2018', 'DD-MON-YYYY')
AND to_date(inv_time, 'HH:MI:SS AM') > to_Date('12:00', 'HH24:MI');
If you get rid of the time column and just have the date column then all you need is:
SELECT * FROM inv
WHERE inv_date > to_Date('01-NOV-2018 12:00:00', 'DD-MON-YYYY HH24:MI:SS');
|
|
|
|
|
Re: Display Time [message #673659 is a reply to message #673657] |
Wed, 28 November 2018 08:46 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
nice catch BlackSwan, yes the command should be
select * from INV
WHERE TO_CHAR(INV_TIME,'HH24MISS') BETWEEN '000029' AND '190003';
|
|
|
Re: Display Time [message #673660 is a reply to message #673659] |
Wed, 28 November 2018 09:19 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Except that inv_time is currently a varchar2. So you need to to_date it first:
select * from INV
WHERE TO_CHAR(to_Date(INV_TIME, 'HH:MI:SS AM'),'HH24MISS') BETWEEN '000029' AND '190003';
|
|
|