compare dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: compare dates

  1. #1
    Join Date
    Oct 2000
    Posts
    139
    Hi

    Is it possible to compare dates in WHERE clause? For example find out who was hired after December 1999 in a Employee table...

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Sure, as long as you keep up with the format.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Oct 2000
    Posts
    139
    Hi

    I wrote

    select ename from emp
    where hiredate > 17/02/99 but I got errors? Whad did I do wrong?

  4. #4
    Join Date
    Feb 2001
    Posts
    184
    Check the Date Format stored in Database and Date is always encloed in single Quotes.

    Usually Date is stored as DD-MON-YY Even if you have any other Format but need to use single Quotes.

    Thanks

  5. #5
    Join Date
    Oct 2000
    Posts
    139
    How do I find out the date format?

  6. #6
    Join Date
    Feb 2001
    Posts
    184
    Easy way.
    Select Sysdate from Dual;

    You will be able to see how the date format is in Database, when you want to use date column, you have to give the same format, unless you apply any Function.
    Thanks

    It may give you like this
    SYSDATE
    =========
    06-MAR-01

  7. #7
    Join Date
    Oct 2000
    Posts
    123
    Do one thing:

    select * from nls_session_parameters;

    It will give you every parameter formats.

    Take care

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The general rule for any string to date (and vice versa) conversion shoul be:

    NEVER RELY ON DEFAULT DATE FORMAT (whther on server or on client side)!!!!!!

    Always (without exception!) use explicite TO_DATE/TO_CHAR conversion with proper format mask. Oracle has to perform conversion in any case, so it is better that you tell it in advance what format mask it should use.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yea, verily!!

    Well spoken, brother Jurij!!!!

    This would make your query:

    select ename from emp
    where hiredate > TO_DATE('17/02/99', 'DD/MM/YY')

    Of course, since you never want to specify only 2-digit years either, might I suggest:

    select ename from emp
    where hiredate > TO_DATE('17/02/1999', 'DD/MM/YYYY')

    - Chris



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width