Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Question Unanswered: Problem querying date fields

    Hi,

    I have this "little" problem. The following query should return lines which met the condition ttfacp200100.t$docd >= 2002/01/01
    But, it's returning results from 1999 and then 2002 and 2003... Anyone might know why? Pleas help

    Regards,
    Miguel

    ===========================================
    select ttfacp200100.t$docd dataDoc,
    ttfacp200100.t$ttyp || ttfacp200100.t$ninv documento, ttfacp200100.t$dued dataVenc,
    ttfacp200100.t$isup numFactFornec,
    ttfacp200100.t$amth valor,
    ttfacp200100.t$ccur div
    from ttfacp200100, ttccom020100
    where
    ttccom020100.t$fovn = '501883720' and
    ttccom020100.t$suno = ttfacp200100.t$suno and
    ttfacp200100.t$docn = 0 and
    to_date(ttfacp200100.t$docd,'YY.MM.DD') >= to_date(20020101,'YYYYMMDD')
    and ttfacp200100.t$balh = 0

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

    Re: Problem querying date fields

    Originally posted by msantos
    Hi,

    I have this "little" problem. The following query should return lines which met the condition ttfacp200100.t$docd >= 2002/01/01
    But, it's returning results from 1999 and then 2002 and 2003... Anyone might know why? Pleas help

    Regards,
    Miguel

    ===========================================
    select ttfacp200100.t$docd dataDoc,
    ttfacp200100.t$ttyp || ttfacp200100.t$ninv documento, ttfacp200100.t$dued dataVenc,
    ttfacp200100.t$isup numFactFornec,
    ttfacp200100.t$amth valor,
    ttfacp200100.t$ccur div
    from ttfacp200100, ttccom020100
    where
    ttccom020100.t$fovn = '501883720' and
    ttccom020100.t$suno = ttfacp200100.t$suno and
    ttfacp200100.t$docn = 0 and
    to_date(ttfacp200100.t$docd,'YY.MM.DD') >= to_date(20020101,'YYYYMMDD')
    and ttfacp200100.t$balh = 0
    A 2-digit year with YY mask is assumed to be in the current century:

    SQL> select to_date('99.12.31','YY.MM.DD') from dual;

    TO_DATE('99
    -----------
    31-DEC-2099

    If you use RR instead of YY then Oracle treats year values 50-99 as being in the LAST century:

    SQL> select to_date('99.12.31','RR.MM.DD') from dual;

    TO_DATE('99
    -----------
    31-DEC-1999

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Re: Problem querying date fields

    Kindly use the following condition and you would be fine

    ttfacp200100.t$docd >= to_date('2002/01/01','YYYY/MM/DD')

    Thanx and Regards
    Aruneesh

    Originally posted by msantos
    Hi,

    I have this "little" problem. The following query should return lines which met the condition ttfacp200100.t$docd >= 2002/01/01
    But, it's returning results from 1999 and then 2002 and 2003... Anyone might know why? Pleas help

    Regards,
    Miguel

    ===========================================
    select ttfacp200100.t$docd dataDoc,
    ttfacp200100.t$ttyp || ttfacp200100.t$ninv documento, ttfacp200100.t$dued dataVenc,
    ttfacp200100.t$isup numFactFornec,
    ttfacp200100.t$amth valor,
    ttfacp200100.t$ccur div
    from ttfacp200100, ttccom020100
    where
    ttccom020100.t$fovn = '501883720' and
    ttccom020100.t$suno = ttfacp200100.t$suno and
    ttfacp200100.t$docn = 0 and
    to_date(ttfacp200100.t$docd,'YY.MM.DD') >= to_date(20020101,'YYYYMMDD')
    and ttfacp200100.t$balh = 0

  4. #4
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Re: Problem querying date fields

    Originally posted by andrewst
    A 2-digit year with YY mask is assumed to be in the current century:

    SQL> select to_date('99.12.31','YY.MM.DD') from dual;

    TO_DATE('99
    -----------
    31-DEC-2099

    If you use RR instead of YY then Oracle treats year values 50-99 as being in the LAST century:

    SQL> select to_date('99.12.31','RR.MM.DD') from dual;

    TO_DATE('99
    -----------
    31-DEC-1999
    That solved it. Thanks a lot Andrew.

    Regards,
    Miguel

  5. #5
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Re: Problem querying date fields

    Originally posted by aruneeshsalhotr
    Kindly use the following condition and you would be fine

    ttfacp200100.t$docd >= to_date('2002/01/01','YYYY/MM/DD')

    Thanx and Regards
    Aruneesh
    Thanks,

    I've tried and it works fine.

    Thanks again.

    Miguel

  6. #6
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Re: Problem querying date fields

    One question.

    What interface do you use to query oracle? SQL Plus editor? SQL Plus Worksheet? Any other?

    I use both above and i think they both are somewhat primitive.... do you have any suggestions?

    Miguel

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

    Re: Problem querying date fields

    Originally posted by msantos
    Thanks,

    I've tried and it works fine.

    Thanks again.

    Miguel
    Right - if ttfacp200100.t$docd is of data type DATE then you should do it this way. My 'RR.MM.DD' suggestion was based on the assumption that ttfacp200100.t$docd was a VARCHAR2 column containing a string like '99.12.31', which was implied by the SQL shown.

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Yet Another SQL (yasql)

    Miguel
    I found this pretty good SQL editor called yasql, (yet another SQL). At my work place, I am the only one using this editor, but rest of the crowd is just hanging around with sqlplus which is awful.

    yasql provides you with automatic tablename, fieldname completions; command scrolling, history ... and also does wonders to the output for select statements.

    I am sure you would find it very useful and pleasant to use.
    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Jul 2003
    Location
    OFR, PT
    Posts
    16

    Unhappy Re: Yet Another SQL (yasql)

    Originally posted by aruneeshsalhotr
    Miguel
    I found this pretty good SQL editor called yasql, (yet another SQL). At my work place, I am the only one using this editor, but rest of the crowd is just hanging around with sqlplus which is awful.

    yasql provides you with automatic tablename, fieldname completions; command scrolling, history ... and also does wonders to the output for select statements.

    I am sure you would find it very useful and pleasant to use.
    Thanx and Regards
    Aruneesh
    I found something with that name in sourceforge.net, but is meant to run under POSIX.... and I use Windows. Is there a windows version?

    Regards,
    Miguel

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    Use TOAD.
    It's free and very handy.

    www.quest.com
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Toad does not respect the DB issued NLS settings. You can introduce a third variable into what is already a difficult area. It also configures itself with a default format setting according to your locale.

    SERVER - init.ora
    ===========
    nls_territory = "UNITED KINGDOM"
    nls_language = "ENGLISH"

    Local Win32 machines - in the registry
    ========================
    NLS_LANG = "ENGLISH_UNITED KINGDOM.WE8ISO8859P1"

    Toad Configuration (if you can find it)
    ========================
    Date format MM/DD/YYYY

    All three can be different.

    You can check them as follows.....

    select * from all_objects where object_name like 'NLS%';

    select * from nls_database_parameters where parameter = 'NLS_DATE_FORMAT'

    select * from nls_instance_parameters where parameter = 'NLS_DATE_FORMAT'

    select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'

    Hth
    Bill

Posting Permissions

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