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!

• snomadj

  SSCarpal Tunnel

  Points: 4491

  More actions

  March 22, 2016 at 4:05 am

  #1866694

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

  Thank you!!

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  March 22, 2016 at 4:19 am

  #1866696

  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

  March 22, 2016 at 5:53 am

  #1866719

  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

  March 22, 2016 at 7:31 am

  #1866768

  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: 32066

  More actions

  March 22, 2016 at 10:19 am

  #1866854

  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

  November 14, 2016 at 6:57 am

  #1912245

  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.