Home » SQL & PL/SQL » SQL & PL/SQL » Query giving wrong result on time
Query giving wrong result on time [message #299671] Tue, 12 February 2008 23:09 Go to next message
naz_2008
Messages: 5
Registered: February 2008
Location: OMAN
Junior Member
Hi ALL,
i have writen the query to get the results on date between 01-jan-2008 and 31-jan-2008 and time 9 am to 9:30 AM, but the quety result showing all AM and PM data. here is the my query

select To_Char(INVH_CR_DT, 'DD-MON-YY HH12:MI:SS'),INVI_ITEM_CODE,invi_qty
from ot_invoice_head,ot_invoice_item,om_item
where invi_invh_sys_id=invh_sys_id
and invi_item_code=item_code
AND INVH_CR_DT BETWEEN '01-JAN-2008' AND '31-JAN-2008'
and to_char(INVH_CR_DT,'HH:MM:SS AM')>='09:00:00 AM'
AND to_char(INVH_CR_DT,'HH:MM:SS AM')<='09:30:00 AM'
and invh_txn_code='CM001'



can any one help me to get the result time between 9 AM to 9:30 am only

Thanks in advance
Re: Query giving wrong result on time [message #299704 is a reply to message #299671] Wed, 13 February 2008 00:05 Go to previous messageGo to next message
divyangkumar.shah
Messages: 1
Registered: January 2007
Junior Member
You are trying with below query

select * from emp
where hiredate between to_date('12/17/1980','mm/dd/yyyy') and to_date('01/23/1982','mm/dd/yyyy')
and to_char(hiretime,'hh:mi:ss AM') between '10:26:48 AM' and '11:55:48 AM'

I feel, your query is also fine. Recheck your query.
Re: Query giving wrong result on time [message #299709 is a reply to message #299704] Wed, 13 February 2008 00:16 Go to previous messageGo to next message
naz_2008
Messages: 5
Registered: February 2008
Location: OMAN
Junior Member
I have tried in diffrence way but the result showing

01-JAN-08 09:03:14 PM GEL348F03 1
01-JAN-08 09:25:57 PM GEL2147F01 1
01-JAN-08 09:31:51 PM CAL4029BLK37 1
02-JAN-08 09:52:15 AM CEY62004WHT36 1
02-JAN-08 09:08:13 PM BTH4216116BLK 1
02-JAN-08 09:08:13 PM CMSST1LWHT 1
03-JAN-08 09:55:58 AM RKR2357326 1
03-JAN-08 09:30:44 PM ITL193BLK 1
05-JAN-08 09:54:29 AM CYO400028BLK 1
05-JAN-08 09:54:29 AM RAM2905MLT38 1
05-JAN-08 09:16:07 PM ROS13304BLK 1

TO_CHAR(INVH_CR_DT,'D INVI_ITEM_CODE INVI_QTY
--------------------- -------------------- ----------
06-JAN-08 09:52:09 AM GEL343F01 1
06-JAN-08 09:53:42 AM GEL340E05 1
06-JAN-08 09:56:09 AM HOT6209161BLK 1
07-JAN-08 09:44:22 AM GEL217F03 1
07-JAN-08 09:44:22 AM AET2013BLK 1
07-JAN-08 09:19:49 PM AET2007BLK 1
09-JAN-08 09:19:18 AM GEL343F01 1
10-JAN-08 09:34:36 AM CYOMINELLIDBL 1
10-JAN-08 09:34:36 AM CYOMINELLIOLV 1
11-JAN-08 09:00:01 PM BRI486DVWHT 1
11-JAN-08 09:00:01 PM BTH4216116BLK 1

query getting AM and PM, i want get the result only AM.
please can any one help me on this
Re: Query giving wrong result on time [message #299724 is a reply to message #299709] Wed, 13 February 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ String '09:20:00 PM' is less than than string '09:30:00 AM'
2/ "INVH_CR_DT BETWEEN '01-JAN-2008' AND '31-JAN-2008'" comparing date to strings is a BUG

You work on date and time, use date and time comparison.

Forgot: please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 13 February 2008 00:43]

Report message to a moderator

Re: Query giving wrong result on time [message #299734 is a reply to message #299671] Wed, 13 February 2008 01:06 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
First of all, I'm a bit confused:

SQL> SELECT TO_CHAR(SYSDATE,'HH12:MI:SS') FROM DUAL ;

TO_CHAR(SYSDATE,'HH12:MI:SS')
-----------------------------
07:30:35

The AM or PM bit doesn't show.

SQL> SELECT TO_CHAR(SYSDATE,'HH12:MI:SS AM') FROM DUAL ;

TO_CHAR(SYSDATE,'HH12:MI:SSAM')
-------------------------------
07:30:35 AM

Ah, now the AM bit appears.

Let's do some experimenting:
with qry as (
    select to_date('01012008211959','DDMMYYYYHH24MISS') timestamp from dual
    union
    select to_date('01012008091959','DDMMYYYYHH24MISS') from dual
    )
select  TO_CHAR(timestamp,'DD/MM/YYYY HH12:MI:SS AM')
from    qry      
where   TO_CHAR(timestamp,'HH24:MI:SS') between '09:00:00' and '09:30:00'

TO_CHAR(timestamp,'DD/MM/YYYYHH12:MI:SSAM')
-------------------------------------------
01/01/2008 09:19:59 AM


In your case it would be better to use a 24-hour clock in your WHERE-clause

But I guess there are plenty good solution - and even better ones too - around

HTH
Marc







Re: Query giving wrong result on time [message #299751 is a reply to message #299734] Wed, 13 February 2008 02:04 Go to previous messageGo to next message
naz_2008
Messages: 5
Registered: February 2008
Location: OMAN
Junior Member
I have tried still didnt work, please some can help me on this

thankin you
Re: Query giving wrong result on time [message #299752 is a reply to message #299751] Wed, 13 February 2008 02:10 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
naz_2008 wrote on Wed, 13 February 2008 09:04
I have tried still didnt work, please some can help me on this

thankin you


As you don't share what you have tried, we can't figure out what you did wrong so we can't correct it.

So please let us know what you have tried so far, including full error messages.

We could be experts on the matter but we sure are not clearvoyant Wink
Re: Query giving wrong result on time [message #299754 is a reply to message #299751] Wed, 13 February 2008 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you take into account my post?
If you follow it, it is obvious how you should modify your query.

Regards
Michel
Re: Query giving wrong result on time [message #299755 is a reply to message #299752] Wed, 13 February 2008 02:17 Go to previous messageGo to next message
naz_2008
Messages: 5
Registered: February 2008
Location: OMAN
Junior Member
I tried the query as
select To_Char(INVH_CR_DT, 'DD-MON-YY HH12:MI:SS'),INVI_ITEM_CODE,invi_qty
from ot_invoice_head,ot_invoice_item,om_item
where invi_invh_sys_id=invh_sys_id
and invi_item_code=item_code
AND TO_CHAR(INVH_CR_DT,'DD-MON-YYYY HH12:MI:SS AM')>='01-JAN-2008 09:00:00 AM'
AND TO_CHAR(INVH_CR_DT,'DD-MON-YYYY HH12:MI:SS AM')<='15-JAN-2008 09:30:00 AM'
and invh_txn_code='CM001'

form the above query its showing all the record from 01-mar-2005 on words

thanks
Re: Query giving wrong result on time [message #299758 is a reply to message #299755] Wed, 13 February 2008 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
String '09:20:00 PM' is less than than string '09:30:00 AM'

Quote:
You work on date and time, use date and time comparison.

Is this not clear?

Regards
Michel

[Updated on: Wed, 13 February 2008 02:21]

Report message to a moderator

Re: Query giving wrong result on time [message #299760 is a reply to message #299671] Wed, 13 February 2008 02:25 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
AND TO_CHAR(INVH_CR_DT,'DD-MON-YYYY HH12:MI:SS AM')>='01-JAN-2008 09:00:00 AM'
AND TO_CHAR(INVH_CR_DT,'DD-MON-YYYY HH12:MI:SS AM')<='15-JAN-2008 09:30:00 AM'


Try to understand that you are not comparing date and time but instead you are comparing strings
And strings are a completely different datatype as date/time !

To accomplish what you need (with the information you are providing us)
Filter out on date first
AND INVH_CR_DT BETWEEN TO_DATE('01012008','DDMMYYYY') AND TO_DATE('15012008','DDMMYYYY')

This will yield all those records falling between those two dates.

Comparing the time-frame should be done - in your case - string based (as Michael & I keep telling you Wink )

AND TO_CHAR(INVH_CR_DT,'HH24MI') BETWEEN '0900' AND '0930'


@ Michael: I don't think it's clear Wink

Re: Query giving wrong result on time [message #299772 is a reply to message #299760] Wed, 13 February 2008 02:55 Go to previous message
naz_2008
Messages: 5
Registered: February 2008
Location: OMAN
Junior Member
Thanks lot for your kind help Michael
its working fine
BRS
Previous Topic: need the values of a variable inside a for loop
Next Topic: please explain this statement
Goto Forum:
  


Current Time: Sat Dec 10 20:51:36 CST 2016

Total time taken to generate the page: 0.12738 seconds