CODEWITHSUNDEEP
oracle-databaseoracle11g
kinkajou
kinkajou

Reputation: 3728

Getting all data between certain date time

I am reading data from remote oracle database with read only access. and I want to query all data between some time frame. I actually wanted to query between mid-night and current time. so the query I was using :

TO_DATE(to_char(sysdate, 'MM-dd-yyyy')||'00:00:00','MM-dd-yyyy HH24:MI:SS' ) 
AND 
TO_DATE(to_char(sysdate, 'MM-dd-yyyy HH24:MI:SS'),'MM-dd-yyyy HH24:MI:SS' )

But the query 

 select TO_DATE(to_char(sysdate, 'MM-dd-yyyy HH24:MI:SS'),'MM-dd-yyyy HH24:MI:SS' ) from dual

is returning only 18-APR-12 not the time. How do I get time too?

I am running :

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

But I don't want to run this everytime any other way to overcome this problem?

Upvotes: 2

Views: 1132

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

What you see is not what is really there.
Oracle holds the date as a number represanting the date and the way you see it when you query it depends on your NLS parameters.
The fact that you don't see the time doesn't mean there is no time.

as for your query I'd do something like this: 

... BETWEEN trunc(sysdate) AND sysdate

I see no use in casting a Date to a string and then back to a Date with the same format ...

Upvotes: 5

Related Questions