Home » SQL & PL/SQL » SQL & PL/SQL » help required in SQL date condition (merged 3) (oracle 9.2.0.4.0, solaris )
help required in SQL date condition (merged 3) [message #439737] Tue, 19 January 2010 05:12 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi Folks,

If I run the below query, it is giving correct output.

select T1100_KEY1,T1100_KEY2,to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss'),T1100_MESSAGE_TYPE
from S1100_XML_DATA where T1100_MESSAGE_TYPE = 'SUBMIT-TRANSACTION'
and to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss') > '18/01/2010 23:00:00'
and to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss') < '18/01/2010 23:59:59';

T1100_KEY1           T1100_KEY2      TO_CHAR(T1100_DATE_ T1100_MESSAGE_TYPE
-------------------- --------------- ------------------- ------------------
11535336             02076131602     18/01/2010 23:15:38 SUBMIT-TRANSACTION
11535333             01132394636     18/01/2010 23:38:45 SUBMIT-TRANSACTION


But In the above query, if I change the date & time from '18/01/2010 23:00:00' to '19/01/2010 00:00:00', it is giving the wrong output as below.

SQL> select T1100_KEY1,T1100_KEY2,to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss'),T1100_MESSAGE_TYPE
from S1100_XML_DATA where T1100_MESSAGE_TYPE = 'SUBMIT-TRANSACTION'
and to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss') > '18/01/2010 23:00:00'
and to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss') < '19/01/2010 00:00:00';

T1100_KEY1           T1100_KEY2                                    TO_CHAR(T1100_DATE_ T1100_MESSAGE_TYPE
-------------------- --------------------------------------------- ------------------- -------------------------
8601943              01506463753                                   18/07/2005 15:06:21 SUBMIT-TRANSACTION
9062117              02085436779                                   18/08/2006 14:21:07 SUBMIT-TRANSACTION
9062873              02920367138                                   18/08/2006 21:39:26 SUBMIT-TRANSACTION
8468210              01255886967                                   18/03/2005 07:48:26 SUBMIT-TRANSACTION
8468260              01407741184                                   18/03/2005 08:41:50 SUBMIT-TRANSACTION
8468360              01428648308                                   18/03/2005 09:24:55 SUBMIT-TRANSACTION
8468682              01505863503                                   18/03/2005 11:00:35 SUBMIT-TRANSACTION
.
.
.
.


Where I did mistake? As I am new to oracle, can anybody help me pls?
Re: Help required in SQL date condition [message #439739 is a reply to message #439737] Tue, 19 January 2010 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
it is giving the wrong output as below.

Wrong! It gives the correct output for the question you asked.
String '18/03/2005' is between string '18/01/2010 23:00:00' and string '19/01/2010 00:00:00'.
If you want to compare date then convert the string to dates using TO_DATE.

Regards
Michel
Re: help required in SQL date condition [message #439744 is a reply to message #439737] Tue, 19 January 2010 05:24 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You don't compare dates, you compare characters.

You convert the date to a character with to_char and then you compare it to another char.
Re: help required in SQL date condition (merged 3) [message #440442 is a reply to message #439737] Sun, 24 January 2010 18:19 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi
i hope you are fine.

dear friend when you type the date yourself like to_date('19-01-2010','dd-mm-rrrr') then it is equal to 19-01-2010 00:00:00
if you get the date from sysdate function then it is with the current time.
you should not change the date column value to character instead of this you should change the given value into date format.

if i repair your query then it should b like this.

select T1100_KEY1,T1100_KEY2,to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss'),T1100_MESSAGE_TYPE
from S1100_XML_DATA where T1100_MESSAGE_TYPE = 'SUBMIT-TRANSACTION'
and T1100_DATE_SUBMITTED > to_date('18/01/2010 23:00:00','dd/mm/yyyy hh:mi:ss')
and T1100_DATE_SUBMITTED < to_date('19/01/2010 00:00:00','dd/mm/yyyy hh:mi:ss');


I hope this will solve your Problem.

regards

Muhammad Shahid Mughal
Oracle Developer
Professional's Corner
Faisalabad Pakistan
Re: help required in SQL date condition (merged 3) [message #440459 is a reply to message #440442] Mon, 25 January 2010 01:02 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
shahidmughal wrote on Mon, 25 January 2010 01:19

select T1100_KEY1,T1100_KEY2,to_char(T1100_DATE_SUBMITTED,'DD/MM/YYYY hh24:mi:ss'),T1100_MESSAGE_TYPE
from S1100_XML_DATA where T1100_MESSAGE_TYPE = 'SUBMIT-TRANSACTION'
and T1100_DATE_SUBMITTED > to_date('18/01/2010 23:00:00','dd/mm/yyyy hh:mi:ss')
and T1100_DATE_SUBMITTED < to_date('19/01/2010 00:00:00','dd/mm/yyyy hh:mi:ss');

SQL> select to_date('01-01-2010 23:00:00', 'dd-mm-yyyy hh:mi:ss') from dual;
select to_date('01-01-2010 23:00:00', 'dd-mm-yyyy hh:mi:ss') from dual
               *
ERROR at line 1:
ORA-01849: hour must be between 1 and 12

Your date-mask should use hh24 instead of hh.
Previous Topic: SQL problem
Next Topic: Extract duplicate records.
Goto Forum:
  


Current Time: Sun Dec 04 10:35:42 CST 2016

Total time taken to generate the page: 0.19934 seconds