Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-04-25
16
Medium Priority
?
572 Views
Last Modified: 2014-04-27
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?
0
Comment
Question by:gilnari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 15

Expert Comment

by:Ess Kay
ID: 40023499
why not convert it to millitary time?


simply - if PM add 12 hr
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40023562
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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40023579
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:gilnari
ID: 40023614
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40023625
other, more "basic" question: what data type is "login_date" (date, timestamp, varchar2 ...)?!
0
 

Author Comment

by:gilnari
ID: 40023635
In oracle the defined as DATE
0
 
LVL 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 2000 total points
ID: 40023671
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40023672
In oracle the defined as DATE
Bingo! So, using this your where clause comparison is missing the time part, thus no results ;-)
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40023750
>>  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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40023943
?? 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
 

Author Closing Comment

by:gilnari
ID: 40024638
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40025222
>>"What more will I find when I get through the next 5000 plus lines of code."

many gems?
good luck
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40025676
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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40025693
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40025718
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
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40026073
>> Then, splitting the points would have been more adequate ;-)

Me neither, let's leave it the way it is ;)
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question