Home » SQL & PL/SQL » SQL & PL/SQL » Display Time (11g )
Display Time [message #673641] Wed, 28 November 2018 03:40 Go to next message
glmjoy
Messages: 163
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 #673642 is a reply to message #673641] Wed, 28 November 2018 03:44 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
You need to use the TO_DATE function to qualify your time format.
Re: Display Time [message #673643 is a reply to message #673642] Wed, 28 November 2018 04:04 Go to previous messageGo to next message
glmjoy
Messages: 163
Registered: September 2011
Location: KR
Senior Member
i had use to_date to but date is displaying '01-NOV-2018'
Re: Display Time [message #673644 is a reply to message #673643] Wed, 28 November 2018 04:07 Go to previous messageGo to next message
glmjoy
Messages: 163
Registered: September 2011
Location: KR
Senior Member
1 select * from INV
2* Where to_DAte(INV_TIME,'HH:MI') between '12:00:29 AM' and '07:00:03 PM'
SQL> /
Where to_DAte(INV_TIME,'HH:MI') between '12:00:29 AM' and '07:00:03 PM'
*
ERROR at line 2:
ORA-01843: not a valid month
Re: Display Time [message #673645 is a reply to message #673644] Wed, 28 November 2018 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66324
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You must also apply TO_DATE to constants in WHERE clause and your format model must match your data: '12:00:35 AM' vs 'HH:MI'.

Re: Display Time [message #673648 is a reply to message #673641] Wed, 28 November 2018 06:12 Go to previous messageGo to next message
EdStevens
Messages: 1050
Registered: September 2013
Senior Member
Your design is fundamentally flawed.

In oracle, the DATE data type includes the time, down to the second. There is no need to keep 'time' as a separate entity. Doing so only complicates everything.

In addition, your failure to correctly use to_date and to_char when handling date and time only compounds the error.

Read this: https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/
Re: Display Time [message #673649 is a reply to message #673645] Wed, 28 November 2018 06:19 Go to previous messageGo to next message
cookiemonster
Messages: 13554
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 #673656 is a reply to message #673649] Wed, 28 November 2018 08:17 Go to previous messageGo to next message
Bill B
Messages: 1846
Registered: December 2004
Senior Member
If you want all rows between 12:00:29 am and 07:00:03 pm for every days you COULD use

select * from INV
WHERE TO_CHAR(INV_TIME,'HH24MISS') BETWEEN '120029' AND '190003';

[Updated on: Wed, 28 November 2018 08:18]

Report message to a moderator

Re: Display Time [message #673657 is a reply to message #673656] Wed, 28 November 2018 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 26483
Registered: January 2009
Location: SoCal
Senior Member
Bill B wrote on Wed, 28 November 2018 06:17
If you want all rows between 12:00:29 am and 07:00:03 pm for every days you COULD use

select * from INV
WHERE TO_CHAR(INV_TIME,'HH24MISS') BETWEEN '120029' AND '190003';
"120029" is "12:09" PM
"000029" is "12:00:29 AM"
Re: Display Time [message #673659 is a reply to message #673657] Wed, 28 November 2018 08:46 Go to previous messageGo to next message
Bill B
Messages: 1846
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 Go to previous message
cookiemonster
Messages: 13554
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';
Previous Topic: Check for status of a computer in the network
Next Topic: Fetching data dynamically in Collections
Goto Forum:
  


Current Time: Sun Apr 21 19:06:07 CDT 2019