SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


oracle noob.


oracle noob.

Author
Message
snomadj
snomadj
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3006 Visits: 779
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
snomadj
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3006 Visits: 779
also is there an osql -L equivalent to locate oracle instances?

Thank you!!
Igor Micev
Igor Micev
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27861 Visits: 5230
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
snomadj
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3006 Visits: 779
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
snomadj
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3006 Visits: 779
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
Jo Pattyn
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25493 Visits: 10652
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
kevaburg
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14966 Visits: 1280
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search