Query on a Date and Time in Oracle not returning a value

I know this is simple but it's Friday and I am brain fried

I have this simple Where clause in my query
WHERE  to_date(s.login_date) >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM')

The record I expcet back has a login date of 04/25/2014 01:15:25 PM

I am not getting the query to return my record.   If I set the query to use 4/24 then I get it but I also get other information I don't want.   Why does it not understand the PM value is greater then the AM value?
gilnariAsked:
Who is Participating?
 
Alexander Eßer [Alex140181]Connect With a Mentor Software DeveloperCommented:
I suppose the column is a date type col, then your query will "fail", see example below:

-- this SQL will work as it should & what's been asked for
select to_date(s.login_date, 'mm/dd/yyyy hh:mi:ss AM') login_date_dt,
       to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM') compare_dt
  from (select '04/25/2014 01:15:25 PM' login_date
          from dual) s
 where to_date(s.login_date, 'mm/dd/yyyy hh:mi:ss AM') >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM');

-- this SQL will NOT work "properly" the way you need it
select to_date(s.login_date) login_date_dt,
       to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM') compare_dt
  from (select to_date('04/25/2014 01:15:25 PM', 'mm/dd/yyyy hh:mi:ss AM') login_date
          from dual) s
 where to_date(s.login_date) >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM');

Open in new window

0
 
Ess KayEntrapenuerCommented:
why not convert it to millitary time?


simply - if PM add 12 hr
0
 
Wasim Akram ShaikCommented:
it will understand.. might be you are not checking it properly.. please retry.. if you still face same issue.. post the data which is missing here
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Gerwin Jansen, EE MVETopic Advisor Commented:
Can you try adding a date format to s.date as well? Like this:

WHERE to_date(s.login_date, 'mm/dd/yyyy hh:mi:ss AM') >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM')

If you don't get a result, can you test and try to get all logon_dates from 'today', like this:

select s.login_date
from your_table s
WHERE to_date(s.login_date > trunc(sysdate));
0
 
gilnariAuthor Commented:
For adding 12 hrs if PM...the way the query works in the code is not that simple to do.   I am looking for a simplier solution then to revamp code to change things to military time.

Here is the query
SELECT s.sample_number, login_date FROM sample s INNER JOIN test t
ON s.sample_number = t.sample_number  
WHERE  to_date(s.login_date) >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM')


Here is what value that should be returned as it looks in the database.  First value is the sample number and the second value is the date / time in the database.
The query above is not return the value.  I am using PL/SQL to run the query.

1667265    4/25/2014 3:00:20 PM
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
other, more "basic" question: what data type is "login_date" (date, timestamp, varchar2 ...)?!
0
 
gilnariAuthor Commented:
In oracle the defined as DATE
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
In oracle the defined as DATE
Bingo! So, using this your where clause comparison is missing the time part, thus no results ;-)
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
>>  is missing the time part
That's why I was suggesting to add a date format to s.date:

WHERE to_date(s.login_date, 'mm/dd/yyyy hh24:mi:ss') >= to_date('04/25/2014 10:14:14', 'mm/dd/yyyy hh24:mi:ss')
0
 
PortletPaulfreelancerCommented:
?? why apply to_date() on s.login_date if it is already a date type ??

The original query works in 11g if to_date() is removed from that field, and there is some data to return

**Query 1**:

    SELECT
         s.sample_number
       , login_date
    FROM sample s
    INNER JOIN test t ON s.sample_number = t.sample_number
    WHERE s.login_date >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM')
    
    
    

**[Results][2]**:
    
    | SAMPLE_NUMBER |                   LOGIN_DATE |
    |---------------|------------------------------|
    |            10 | April, 25 2014 23:00:00+0000 |



  [1]: http://sqlfiddle.com/#!4/3cb1e/2



    
    CREATE TABLE SAMPLE
    	("SAMPLE_NUMBER" int, "LOGIN_DATE" date)
    ;
    
    INSERT ALL 
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (1, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (2, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (3, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (4, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (5, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (6, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (7, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (8, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (9, to_date('2014-04-25 00:00:00','yyyy-mm-dd HH24:mi:ss') )
    	INTO SAMPLE ("SAMPLE_NUMBER", "LOGIN_DATE")
    		 VALUES (10, to_date('2014-04-25 23:00:00','yyyy-mm-dd HH24:mi:ss') )
    SELECT * FROM dual
    ;
    
    CREATE TABLE TEST
    	("SAMPLE_NUMBER" int)
    ;
    
    INSERT ALL 
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (1)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (2)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (3)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (4)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (5)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (6)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (7)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (8)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (9)
    	INTO TEST ("SAMPLE_NUMBER")
    		 VALUES (10)
    SELECT * FROM dual
    ;

Open in new window

0
 
gilnariAuthor Commented:
DOH!!!   I took this code in the application over from someone and the debugging of the other items, I lost focus on the simple things.   Yup removing to_date from the s.login_date does fix the query.   What more will I find when I get through the next 5000 plus lines of code.
0
 
PortletPaulfreelancerCommented:
>>"What more will I find when I get through the next 5000 plus lines of code."

many gems?
good luck
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I took this code in the application over from someone and the debugging of the other items, I lost focus on the simple things.
Continuing someone else's job can often be a pain in the neck ;-)
Good luck :-)

P.S.: Come back if you need more help ;-)
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Hello Gilnari, you've selected #40023671 as an answer to your question, the first part where Alex refers to -- this SQL will work ... contains this where clause:

where to_date(s.login_date, 'mm/dd/yyyy hh:mi:ss AM') >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM');

This is the exact same where clause as I suggested before in #40023579 :

WHERE to_date(s.login_date, 'mm/dd/yyyy hh:mi:ss AM') >= to_date('04/25/2014 10:14:14 AM', 'mm/dd/yyyy hh:mi:ss AM')

So I don't understand why you've chosen Alex's answer, looks like I suggested the correct answer earlier?
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
So I don't understand why you've chosen Alex's answer, looks like I suggested the correct answer earlier?
Then, splitting the points would have been more adequate ;-)
For me, I am not after the points, so what...
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
>> Then, splitting the points would have been more adequate ;-)

Me neither, let's leave it the way it is ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.