oracle noob.

Question

Post reply

oracle noob.

snomadj

SSCarpal Tunnel

Points: 4491
More actions
March 22, 2016 at 3:56 am

#399892

So I no very little of Oracle:
col is a date datatype. There's something about the way Oracle is dealing with this type that I am not understanding.
If I go:
SELECT col FROM tab WHERE date <= '07-Feb-15'
I get 100 + rows.
SELECT col FROM tab WHERE date <= '06-Feb-15'
I get 0+ rows.
SELECT col FROM tab WHERE date = '07-Feb-15'
I get 1 rows.
How do I select all the 7-Feb-15 rows?
Thank you!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 1

also is there an osql -L equivalent to locate oracle instances?

Thank you!!

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

snomadj (3/22/2016)
So I no very little of Oracle:
col is a date datatype. There's something about the way Oracle is dealing with this type that I am not understanding.
If I go:
SELECT col FROM tab WHERE date <= '07-Feb-15'
I get 100 + rows.
SELECT col FROM tab WHERE date <= '06-Feb-15'
I get 0+ rows.
SELECT col FROM tab WHERE date = '07-Feb-15'
I get 1 rows.
How do I select all the 7-Feb-15 rows?
Thank you!

You've done it with the last select statement. Only one row, the other 99 rows are between 06-Feb-15 and 07-Feb-15 excluding the edges of the date range.

Advise is to use BETWEEN and to include the time in the dates.

Igor Micev,My blog: www.igormicev.com

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 3

Thanks for the reply.

I may not have explained myself very well but I have made a bit of progress, when I round the date field I am seeing the results I expect.

SELECT COUNT(*) FROM table WHERE ROUND(myDATEcolumn) = '17-FEB-16'

So myDATEcolumn has a time in it, that I can't see.

Is there some kind of set option, or system option that hides the time from a date field?

How can I see the time in this field.

If I just do SELECT myDATEcolumn .... it returns in this format '17-Feb-16'

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 4

ah ... the bit of info I was missing was the NLS_DATE_FORMAT.

Once I understood this I could see my true date/times.

Then I used TRUNC to group by.

Thanks for your help

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 5

Use the proper datetype date to compare dates.

SELECT TO_DATE('07-Feb-2015','DD-MON-RRRR'),sysdate

from dual

where TO_DATE('07-Feb-2015','DD-MON-RRRR')<sysdate;

OR

SELECT TO_DATE('07-Feb-2015','DD-MON-RRRR'),sysdate

from dual

where MYDATE<TRUNC(TO_DATE('07-Feb-2015 15:00:00','DD-MON-RRRR HH24:MI:SS'));

*if you use date(time)parameters the TO_DATE isn't necessary

To get 7 februari

SELECT ...

FROM ...

WHERE MYDATE>=TRUNC(TO_DATE('07-Feb-2015','DD-MON-RRRR'))

AND MYDATE<TRUNC(TO_DATE('07-Feb-2015','DD-MON-RRRR'))+1

kevaburg SSCoach Points: 18131 More actions · Answer 6

snomadj (3/22/2016)
also is there an osql -L equivalent to locate oracle instances?
Thank you!!

There is no osql -L for Oracle but you can use TNSPING to check connectivity if you know the SERVICE_NAME of the Instance you want to connect to.