home *** CD-ROM | disk | FTP | other *** search
/ NetNews Usenet Archive 1992 #31 / NN_1992_31.iso / spool / comp / database / oracle / 2633 < prev    next >
Encoding:
Text File  |  1992-12-23  |  1.7 KB  |  56 lines
  1. Newsgroups: comp.databases.oracle
  2. Path: sparky!uunet!munnari.oz.au!yarrina.connect.com.au!cmutual.com.au!aaj
  3. From: aaj@cmutual.com.au (Tony Jambu)
  4. Subject: Re: Question using BETWEEN/AND cond with NEXT_DAY/DAY math
  5. Message-ID: <1992Dec24.032157.4743@cmutual.com.au>
  6. Sender: aaj@cmutual.com.au (Tony Jambu)
  7. Organization: Colonial Mutual Group 
  8. References:  <echoy.724882423@cunews>
  9. Date: Thu, 24 Dec 1992 03:21:57 GMT
  10. Lines: 44
  11.  
  12.  
  13. In article <echoy.724882423@cunews>, echoy@alfred.carleton.ca (Eugene Choy)
  14. writes:
  15. >
  16. stuff deleted
  17. >
  18. > Now, consider this statement 
  19. >    (assume NEXT_DAY(sysdate,'sunday') returns 3-jan-93):
  20. > ----------------------------
  21. > SELECT 'ok' FROM DUAL 
  22. > WHERE
  23. >    to_date('1-jan-93','dd-mm-yy') BETWEEN
  24. >    NEXT_DAY(sysdate,'sunday')-2 AND
  25. >    NEXT_DAY(sysdate,'sunday');
  26. > ----------------------------
  27. > this does not return 'ok'!!!!!
  28. > why is this the case? since,     
  29. >    NEXT_DAY(sysdate,'sunday')-2                 means 01-jan-93
  30. >    NEXT_DAY(sysdate,'sunday')                   means 03-jan-93
  31. > 
  32.  
  33. I know you said 'assume NEXT_DAY(sysdate,'sunday') returns 3-jan-93' but
  34. I believe if you run the query on between the 27th Dec 1992 and 2nd Jan 1993
  35. you
  36. should get the right answer.
  37.  
  38. Why not try
  39.  
  40. SELECT 'ok' FROM DUAL
  41. WHERE
  42.    to_date('26-DEC-92','dd-mon-yy') BETWEEN
  43.    NEXT_DAY(sysdate,'sunday')-2 AND
  44.    NEXT_DAY(sysdate,'sunday');
  45. now.
  46.  
  47. It gives the correct result.
  48.  
  49.  
  50.  
  51. -- 
  52.  _____       ________ / ____ |Tony Jambu, Database Administrator
  53.   /_  __       /_ __ /       |Colonial Mutual Invest Mgmt Aust (ACN 004021809)
  54.  /(_)/ ((_/ \_/(///(/_)/_(   |EMAIL:  TJambu@cmutual.com.au
  55.  \_______/                   |PHONE:  +61-3-6418448       FAX:  +61-3-6076198
  56.