Results 1 to 3 of 3

Thread: query help

  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Exclamation Unanswered: query help

    Hi, I'm not sure if my query is well done.

    I need to select from a table the registers where the date of the field TIP_FECHA_FIN have 30+ days since sysdate. The TIP_FECHA_FIN is a DATE field.

    I've tried with the following query.

    select tip_fecha_fin from btdttip where tip_fecha_fin < (sysdate-30);

    If the sysdate-30 is 14-JUN-03

    I got the following result.

    SQL> select tip_fecha_fin from btdttip where tip_fecha_fin < (sysdate-30);

    TIP_FECHA
    ---------
    01-JUN-03
    13-JUN-03
    14-JUN-03
    20-JUN-03
    20-JUN-03
    20-JUN-03
    19-JUN-03
    30-JUN-03

    8 rows selected.

    What am I doing wrong? How can I fix my query? Please help me.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try:

    select tip_fecha_fin from btdttip where tip_fecha_fin < TRUNC(sysdate-30);

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: query help

    Originally posted by hmarm761
    Hi, I'm not sure if my query is well done.

    I need to select from a table the registers where the date of the field TIP_FECHA_FIN have 30+ days since sysdate. The TIP_FECHA_FIN is a DATE field.

    I've tried with the following query.

    select tip_fecha_fin from btdttip where tip_fecha_fin < (sysdate-30);

    If the sysdate-30 is 14-JUN-03

    I got the following result.

    SQL> select tip_fecha_fin from btdttip where tip_fecha_fin < (sysdate-30);

    TIP_FECHA
    ---------
    01-JUN-03
    13-JUN-03
    14-JUN-03
    20-JUN-03
    20-JUN-03
    20-JUN-03
    19-JUN-03
    30-JUN-03

    8 rows selected.

    What am I doing wrong? How can I fix my query? Please help me.
    I suspect that those dates are in 1903 not 2003. You should always use a 4-digit year to be sure of that:

    alter session set nls_date_format='DD-MON-RRRR';

    You can put that command in a file called login.sql or glogin.sql and have it run automatically every time you start SQL Plus.

Posting Permissions

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