FAQ
Why doesn't this query ever return...

where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

but this query does....

where to_char(run_date) = to_char(to_date('01-03-2001
16:34:59','mm-dd-yyyy hh24:mi:ss'))

and so does this query....

where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

?

TIA

Lisa



Lisa Yates cosnit_at_creighton.edu *
Creighton University *
Omaha, Nebraska *
(402) 280-2419 fax: (402) 280-2573 *
Define the Universe, give 2 examples. *

Search Discussions

  • Armstead, Michael A at Jan 8, 2001 at 7:54 pm
    Because the column run_date is apparently of date datatype.

    The query that doesn't work looks like it compares a date datatype to a
    character datatype which won't evaluate as a match.

    The other two situations compare a character datatype to a character
    datatype, which can evaluate as a match.

    Michael Armstead
    Database Administrator, OCP-Certified
    Corporate & Finance Information Systems
    Glaxo SmithKline
    -----Original Message-----
    From: Lisa Yates [SMTP:cosnit_at_creighton.edu]
    Sent: Monday, January 08, 2001 2:27 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Date comparison question


    Why doesn't this query ever return...

    where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

    but this query does....

    where to_char(run_date) = to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))

    and so does this query....

    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

    ?

    TIA
    Lisa


    ********************************************
    * Lisa Yates cosnit_at_creighton.edu *
    * Creighton University *
    * Omaha, Nebraska *
    * (402) 280-2419 fax: (402) 280-2573 *
    * Define the Universe, give 2 examples. *
    ********************************************


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Jacques Kilchoer at Jan 8, 2001 at 8:04 pm
    This message is in MIME format. Since your mail reader does not understand
    this format, some or all of this message may not be legible.

    ------_=_NextPart_001_01C079AE.252DC1C0
    Content-Type: text/plain;

    charset="iso-8859-1"
    Content-Transfer-Encoding: quoted-printable
    -----Original Message-----
    From: Lisa Yates
    Sent: lundi, 8. janvier 2001 11:27

    Why doesn't this query ever return...
    =20
    where run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy=20
    hh24:mi:ss')
    =20
    but this query does....
    =20
    where to_char(run_date) =3D to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))
    =20
    and so does this query....
    =20
    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001=20
    16:34:59'
    I was able to duplicate your results by inserting a row in a table with =
    a
    "negative" date (aka a "BCE" date).

    SQL> create table t (d date);

    Table created.

    SQL> insert into t (d) values (to_date ('-2001/01/03 16:34:59', =
    'SYYYY/MM/DD

    HH24:MI:SS'));

    1 row created.

    SQL> select count (*)
    2 from t
    3 where d =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss') =;

    COUNT(*)



    SQL> select count (*)
    2 from t
    3 where to_char(d) =3D to_char(to_date('01-03-2001 =
    16:34:59','mm-dd-yyyy
    hh24:mi:ss'))
    4;

    COUNT(*)

    1

    SQL> select count (*)
    2 from t
    3 where to_char(d,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001 16:34:59' =;

    COUNT(*)

    1
    select count (*)
    from t
    where d =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss');

    Jacques R. Kilcho=EBr
    (949) 754-8816
    Quest Software, Inc.

    8001 Irvine Center Drive
    Irvine, California 92618
    U.S.A.
    http://www.quest.com=20

    ------_=_NextPart_001_01C079AE.252DC1C0
    Content-Type: text/html;

    charset="iso-8859-1"
    Content-Transfer-Encoding: quoted-printable

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

    charset=3Diso-8859-1">

    RE: Date comparison question

    &gt; -----Original Message-----
    &gt; From: Lisa Yates [mailto:cosnit_at_creighton.edu]
    ONT>
    &gt; Sent: lundi, 8. janvier 2001 11:27
    &gt;
    &gt; Why doesn't this query ever return...
    &gt;
    &gt; where run_date =3D to_date('01-03-2001 =

    16:34:59','mm-dd-yyyy

    &gt; hh24:mi:ss')
    &gt;
    &gt; but this query does....
    &gt;
    &gt; where to_char(run_date) =3D =

    to_char(to_date('01-03-2001

    &gt; 16:34:59','mm-dd-yyyy hh24:mi:ss'))
    &gt;
    &gt; and so does this query....
    &gt;
    &gt; where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =

    =3D '01-03-2001

    &gt; 16:34:59'

    I was able to duplicate your results by inserting a =
    row in a table with a &quot;negative&quot; date (aka a &quot;BCE&quot; =
    date).

    SQL&gt; create table t (d date);

    Table created.

    SQL&gt; insert into t (d) values (to_date =
    ('-2001/01/03 16:34:59', 'SYYYY/MM/DD HH24:MI:SS'));

    1 row created.

    SQL&gt; select count (*)
    &nbsp; 2&nbsp; from t
    &nbsp; 3&nbsp; where d =3D to_date('01-03-2001 =

    16:34:59','mm-dd-yyyy hh24:mi:ss');

    &nbsp; COUNT(*)
    ----------
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

    SQL&gt; select count (*)
    &nbsp; 2&nbsp; from t
    &nbsp; 3&nbsp; where to_char(d) =3D =

    to_char(to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss'))
    &nbsp; 4&nbsp;;

    &nbsp; COUNT(*)
    ----------
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

    1

    SQL&gt; select count (*)
    &nbsp; 2&nbsp; from t
    &nbsp; 3&nbsp; where to_char(d,'mm-dd-yyyy =

    hh24:mi:ss') =3D '01-03-2001 16:34:59';

    &nbsp; COUNT(*)
    ----------
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =

    1

    select count (*)
    from t
    where d =3D to_date('01-03-2001 =

    16:34:59','mm-dd-yyyy hh24:mi:ss');

    Jacques R. Kilcho=EBr
    (949) 754-8816
    Quest Software, Inc.

    8001 Irvine Center Drive

    Irvine, California 92618
    U.S.A.
  • Lisa Yates at Jan 8, 2001 at 8:05 pm
    Michael,

    you are correct, the column run_date is of date datatype. But, I thought
    that because I had the to_date function in us it would compare date type
    to date type.

    Lisa
    On Mon, 8 Jan 2001, Armstead, Michael A wrote:

    Because the column run_date is apparently of date datatype.

    The query that doesn't work looks like it compares a date datatype to a
    character datatype which won't evaluate as a match.

    The other two situations compare a character datatype to a character
    datatype, which can evaluate as a match.

    Michael Armstead
    Database Administrator, OCP-Certified
    Corporate & Finance Information Systems
    Glaxo SmithKline

    -----Original Message-----
    From: Lisa Yates [SMTP:cosnit_at_creighton.edu]
    Sent: Monday, January 08, 2001 2:27 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Date comparison question


    Why doesn't this query ever return...

    where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

    but this query does....

    where to_char(run_date) = to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))

    and so does this query....

    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

    ?

    TIA
    Lisa


    ********************************************
    * Lisa Yates cosnit_at_creighton.edu *
    * Creighton University *
    * Omaha, Nebraska *
    * (402) 280-2419 fax: (402) 280-2573 *
    * Define the Universe, give 2 examples. *
    ********************************************


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).


    Lisa Yates cosnit_at_creighton.edu *
    Creighton University *
    Omaha, Nebraska *
    (402) 280-2419 fax: (402) 280-2573 *
    Define the Universe, give 2 examples. *
  • Stephane Faroult at Jan 8, 2001 at 8:28 pm
    Lisa,

    Queries which 'do not return' are usually queries performing full scans
    of death. Since the cases which return happen to be, against common wisdom,
    those where the date column is converted to char, could it be possible that
    you have a function-based index (doing the to_char() conversion) on this
    column ?

    Regards,

    Stephane Faroult
    Oriole Corporation

    Lisa Yates wrote:
    Why doesn't this query ever return...

    where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

    but this query does....

    where to_char(run_date) = to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))

    and so does this query....

    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

    ?

    TIA
    Lisa


    ********************************************
    * Lisa Yates cosnit_at_creighton.edu *
    * Creighton University *
    * Omaha, Nebraska *
    * (402) 280-2419 fax: (402) 280-2573 *
    * Define the Universe, give 2 examples. *
    ********************************************

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
  • William Rogge at Jan 8, 2001 at 8:33 pm
    Because, by default date fields do not return the TIME portion unless
    specifically requested to. So you check against run_date has no time
    portion so a match is not possible.

    -----Original Message-----

    From: Lisa Yates [SMTP:cosnit_at_creighton.edu]
    Sent: Monday, January 08, 2001 2:27 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Date comparison question

    Why doesn't this query ever return...

    where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

    but this query does....

    where to_char(run_date) = to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))

    and so does this query....

    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

    ?

    TIA

    Lisa

    Lisa Yates cosnit_at_creighton.edu *
    Creighton University *
    Omaha, Nebraska *
    (402) 280-2419 fax: (402) 280-2573 *
    Define the Universe, give 2 examples. *

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • William Beilstein at Jan 8, 2001 at 8:42 pm
    The reason is that a date is actually stored as a number with the time =
    being stored as the fraction of 1, for example noon is .5

    When you specify an exact time using the to_date function, you will not =
    get the exact same fractional value as you would with a division of =
    1/86400 (one second past midnight)
    Lisa Yates 01/08/01 03:06PM >>>
    Michael,

    you are correct, the column run_date is of date datatype. But, I thought
    that because I had the to_date function in us it would compare date type
    to date type.

    Lisa
    On Mon, 8 Jan 2001, Armstead, Michael A wrote:

    Because the column run_date is apparently of date datatype.=20
    =20
    The query that doesn't work looks like it compares a date datatype to a
    character datatype which won't evaluate as a match.
    =20
    The other two situations compare a character datatype to a character
    datatype, which can evaluate as a match.
    =20
    Michael Armstead
    Database Administrator, OCP-Certified
    Corporate & Finance Information Systems
    Glaxo SmithKline
    =20
    =20
    -----Original Message-----
    From: Lisa Yates [SMTP:cosnit_at_creighton.edu]=20
    Sent: Monday, January 08, 2001 2:27 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Date comparison question
    =20
    =20
    Why doesn't this query ever return...
    =20
    where run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss=
    ')
    =20
    but this query does....
    =20
    where to_char(run_date) =3D to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))
    =20
    and so does this query....
    =20
    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001 =
    16:34:59'
    =20
    ?
    =20
    TIA
    =20
    Lisa
    =20
    =20
    ********************************************
    * Lisa Yates cosnit_at_creighton.edu *=20
    * Creighton University *
    * Omaha, Nebraska *
    * (402) 280-2419 fax: (402) 280-2573 *
    * Define the Universe, give 2 examples. *
    ********************************************
    =20
    =20
    --=20
    Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
    --=20
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu=20
    =20
    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    =20
    =20
    =20

    Lisa Yates cosnit_at_creighton.edu *=20
    Creighton University *
    Omaha, Nebraska *
    (402) 280-2419 fax: (402) 280-2573 *
    Define the Universe, give 2 examples. *

    --=20
    Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
    --=20
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu=20

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
  • Regina Harter at Jan 8, 2001 at 9:17 pm

    At 12:32 PM 1/8/01 -0800, you wrote:
    Because, by default date fields do not return the TIME portion unless
    specifically requested to. So you check against run_date has no time
    portion so a match is not possible.
    Sorry, but this is untrue. We have run into this many times. Even though
    the default date format for displaying dates does not include time, if a
    date field contains a time, it will still use the time portion when
    comparing it to another date.
    -----Original Message-----
    From: Lisa Yates [SMTP:cosnit_at_creighton.edu]
    Sent: Monday, January 08, 2001 2:27 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Date comparison question


    Why doesn't this query ever return...

    where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

    but this query does....

    where to_char(run_date) = to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))

    and so does this query....

    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

    ?

    TIA
    Lisa


    ********************************************
    * Lisa Yates cosnit_at_creighton.edu *
    * Creighton University *
    * Omaha, Nebraska *
    * (402) 280-2419 fax: (402) 280-2573 *
    * Define the Universe, give 2 examples. *
    ********************************************


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: William Rogge
    INET: William_Rogge_at_voltek.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
  • Igor Neyman at Jan 8, 2001 at 9:41 pm
    William,

    You are wrong.
    In Oracle "Date" is stored in fixed-length fields of 7 bytes each,
    corresponding to century, year, month,day, hour, minute, and second (time
    portion - counting after midnight).

    Igor Neyman, OCP DBA
    Perceptron, Inc.
    (734)414-4627
    ineyman_at_perceptron.com

    Original Message -----
    From: "William Beilstein"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, January 08, 2001 3:46 PM
    Subject: RE: Date comparison question

    The reason is that a date is actually stored as a number with the time being
    stored as the fraction of 1, for example noon is .5

    When you specify an exact time using the to_date function, you will not get
    the exact same fractional value as you would with a division of 1/86400 (one
    second past midnight)
    Lisa Yates 01/08/01 03:06PM >>>
    Michael,

    you are correct, the column run_date is of date datatype. But, I thought
    that because I had the to_date function in us it would compare date type
    to date type.

    Lisa
    On Mon, 8 Jan 2001, Armstead, Michael A wrote:

    Because the column run_date is apparently of date datatype.

    The query that doesn't work looks like it compares a date datatype to a
    character datatype which won't evaluate as a match.

    The other two situations compare a character datatype to a character
    datatype, which can evaluate as a match.

    Michael Armstead
    Database Administrator, OCP-Certified
    Corporate & Finance Information Systems
    Glaxo SmithKline

    -----Original Message-----
    From: Lisa Yates [SMTP:cosnit_at_creighton.edu]
    Sent: Monday, January 08, 2001 2:27 PM
    To: Multiple recipients of list ORACLE-L
    Subject: Date comparison question


    Why doesn't this query ever return...

    where run_date = to_date('01-03-2001 16:34:59','mm-dd-yyyy hh24:mi:ss')

    but this query does....

    where to_char(run_date) = to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))

    and so does this query....

    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') = '01-03-2001 16:34:59'

    ?

    TIA
    Lisa


    ********************************************
    * Lisa Yates cosnit_at_creighton.edu *
    * Creighton University *
    * Omaha, Nebraska *
    * (402) 280-2419 fax: (402) 280-2573 *
    * Define the Universe, give 2 examples. *
    ********************************************


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).
    Lisa Yates cosnit_at_creighton.edu *
    Creighton University *
    Omaha, Nebraska *
    (402) 280-2419 fax: (402) 280-2573 *
    Define the Universe, give 2 examples. *

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Lisa Yates
    INET: cosnit_at_creighton.edu

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from). You may
    also send the HELP command for other information (like subscribing).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: William Beilstein
    INET: BeilstWH_at_OBG.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
  • Jacques Kilchoer at Jan 8, 2001 at 10:01 pm
    This message is in MIME format. Since your mail reader does not understand
    this format, some or all of this message may not be legible.

    ------_=_NextPart_001_01C079BE.8B0A7780
    Content-Type: text/plain;

    charset="iso-8859-1"
    Content-Transfer-Encoding: quoted-printable
    -----Original Message-----
    From: Lisa Yates
    Sent: lundi, 8. janvier 2001 11:27
    =20
    Why doesn't this query ever return...
    =20
    where run_date =3D to_date('01-03-2001 16:34:59','mm-dd-yyyy=20
    hh24:mi:ss')
    =20
    but this query does....
    =20
    where to_char(run_date) =3D to_char(to_date('01-03-2001
    16:34:59','mm-dd-yyyy hh24:mi:ss'))
    =20
    and so does this query....
    =20
    where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =3D '01-03-2001=20
    16:34:59'
    Well, I guess my suggestion that run_date may contain a "BCE" (before =
    common
    era) date was not the answer to your problem. Maybe I misunderstood the
    issue. When you say "why doesn't the query ever return", do you mean it
    returns zero rows, or do you mean that the query is taking an =
    abnormally
    long time? If it's taking an abnormally long time, an explain plan on =
    the
    two queries may reveal the cause.

    Jacques R. Kilcho=EBr
    (949) 754-8816
    Quest Software, Inc.

    8001 Irvine Center Drive
    Irvine, California 92618
    U.S.A.
    http://www.quest.com

    ------_=_NextPart_001_01C079BE.8B0A7780
    Content-Type: text/html;

    charset="iso-8859-1"
    Content-Transfer-Encoding: quoted-printable

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">

    RE: Date comparison question

    &gt; -----Original Message-----
    &gt; From: Lisa Yates [mailto:cosnit_at_creighton.edu]
    ONT>
    &gt; Sent: lundi, 8. janvier 2001 11:27
    &gt;
    &gt; Why doesn't this query ever return...

    &gt;
    &gt; where run_date =3D to_date('01-03-2001 =
    16:34:59','mm-dd-yyyy
    &gt; hh24:mi:ss')
    &gt;
    &gt; but this query does....
    &gt;
    &gt; where to_char(run_date) =3D =
    to_char(to_date('01-03-2001
    &gt; 16:34:59','mm-dd-yyyy hh24:mi:ss'))
    &gt;
    &gt; and so does this query....
    &gt;
    &gt; where to_char(run_date,'mm-dd-yyyy hh24:mi:ss') =
    =3D '01-03-2001
    &gt; 16:34:59'

    Well, I guess my suggestion that run_date may contain =
    a &quot;BCE&quot; (before common era) date was not the answer to your =
    problem. Maybe I misunderstood the issue. When you say &quot;why =
    doesn't the query ever return&quot;, do you mean it returns zero rows, =
    or do you mean that the query is taking an abnormally long time? If =
    it's taking an abnormally long time, an explain plan on the two queries =
    may reveal the cause.

    Jacques R. Kilcho=EBr
    (949) 754-8816
    Quest Software, Inc.

    8001 Irvine Center Drive
    Irvine, California 92618
    U.S.A.
    http://www.quest.com" =
    TARGET=3D"_blank">http://www.quest.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 8, '01 at 7:27p
activeJan 8, '01 at 10:01p
posts10
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase