Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL date queries

 
Robin Richardson
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to pull data out of an Oracle database(8.1) and need to be able to pull the data out by date, I have 3 (or more) date columns that I need to be able to reference.
For example I need to know all messages that went out on a certain date. Can anyone give me an idea on how to make this work?
thanks
Robin
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select anycol1, anycol2 from anytable where enroll_date > to_date('20010501235959','YYYYMMDDHH24MISS') and
enroll_date < to_date('20010503000000','YYYYMMDDHH24MISS')
Most systems are storing the date and TIME, so you must be careful. If you system is just storing the date with the same time for every record, then you can do a straight =
If you use trunc or another function on the date field, you will lose the use of the index. Its easier to code, but less efficient.
Dan
 
Robin Richardson
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from table where datecolumn >=(sysdate - 17) and datecolumn <=(sysdate - 16)
This seems to work for now, but that is with only two sampele
dates in the database. Do you know of any reason I should not try using this?
Thanks
Robin
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your not storing the time your fine. But it can bite you if your are.....the sysdate is date and time, and it compares by date and time. It might be excluding records you don't realize.
If you do

select sysdate - 16 from dual
you can see its returning a date and TIME
So anything before that time on that date is excluded

select trunc(sysdate - 16) from dual

you will see the time removed/00:00:00
Dan
[This message has been edited by Daniel Dunleavy (edited June 25, 2001).]
[This message has been edited by Daniel Dunleavy (edited June 25, 2001).]
 
Robin Richardson
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks I think that will work great.
Robin
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic