Forum Stats

  • 3,853,446 Users
  • 2,264,223 Discussions
  • 7,905,362 Comments

Discussions

To_char not working as expected

Abhijit2610
Abhijit2610 Member Posts: 6
edited Oct 11, 2019 9:52AM in SQL & PL/SQL

Hello,

I have below function from which :Effective date is variable if I put colon for the same then it is not reading the variable and if I remove the colon then giving the error ORA-01858: a non-numeric character was found where a numeric was expected

select to_char(to_date(':Effective_date', 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mm') from dual

Please help

Thanks

Tagged:
John Thorton
«1

Answers

  • cormaco
    cormaco Member Posts: 1,989 Silver Crown
    edited Oct 11, 2019 7:35AM

    This is not related to your problem but you probably wanted to write hh:mi or even hh24:mi in your second format model.

    However in your case the time part will always be zero.

  • mathguy
    mathguy Member Posts: 10,677 Blue Diamond
    edited Oct 11, 2019 7:33AM

    What kind of variable is :Effective_date?

    Written with colon like you have, it would be a bind variable, and it should not be enclosed in single-quotes.

    Did you mean to use a SUBSTITUTION variable, in SQL*Plus or something that understands substitution variables (like SQL Developer)? That may need to be in single quotes, but the notation is &Effective_date (with an ampersand, not a colon).

    If Effective_date is not a variable at all, but the name of a column in a table, then it should appear as is (no colon, no ampersand), but not in single quotes.

    After you fix this problem you will find at least one more: in the format model you can't just have hh. It should either be hh24 or hh... AM (it's 22:30 or 10:30 PM). And then a mistake that won't throw an error but will give you wrong results: you use mm for minutes; that should be mi,   mm means month.

  • RogerT
    RogerT Member Posts: 1,859 Gold Trophy
    edited Oct 11, 2019 7:40AM

    So the problem is not to_char (even if the result would not be what you are expecting) it is the to_date function.

    So the questions you have to answer are:

    • what datatype is :effective_date ?
      • if it is a date then get rid of th to_date function
      • if it is a string then provide the correct format the date is stored in that string
    • what is the format of the result string you would like to have?
      • dd/mm/yyyy hh:mm means dayday/monthmonth/yearyearyearyear hour12hour12:monthmonth
    • what do you think will the time portion be, if it is not provided in the input?
  • Abhijit2610
    Abhijit2610 Member Posts: 6
    edited Oct 11, 2019 7:52AM

    effective date is in the format of dd/mm/yyyy

  • mathguy
    mathguy Member Posts: 10,677 Blue Diamond
    edited Oct 11, 2019 7:54AM

    The error the OP reported has nothing to do with any of the questions you suggest. (We may still need to come back and ask those questions; they are just unrelated to the OP's current error.)

    As I pointed out in Reply 2:

    The error he is getting is thrown by finding the literal string   ':Effective_date'  as the first character of TO_DATE. It's a string literal, because it is ENCLOSED IN SINGLE-QUOTES. Within single quotes, the leading colon has no particular meaning.  The single-quotes should be removed, and then let's see what else happens. Perhaps the OP's variable is already in the correct format.

  • mathguy
    mathguy Member Posts: 10,677 Blue Diamond
    edited Oct 11, 2019 7:56AM
    Abhijit2610     Oct 11, 2019 4:52 AM   (in response to mathguy)       effective date is in the format of dd/mm/yyyy

    Did you mean that as a reply to Roger T?   I didn't ask you anything about the format of the variable. I asked you about the kind of variable (bind variable vs. substitution variable).

  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Oct 11, 2019 8:08AM
    Abhijit2610 wrote:
    select to_char(to_date(':Effective_date', 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mm') from dual

    If you want to use Effective_date as a bind varaible just write :Effective_date without single quotes.

  • Abhijit2610
    Abhijit2610 Member Posts: 6
    edited Oct 11, 2019 8:54AM

    Below is the sql query and

    :Effective_date = 01/01/2019 datatype varchar2

    EFFECTIVEDTM = 2019-01-01 00:00

    EXPIRATIONDTM = 2019-01-01 00:00

    SELECT

       EXTPROCESSORNM

    FROM EXTPROCESSOR PR

    INNER JOIN EXTPROCSREMPMM PRA

        ON PR.EXTPROCESSORID = PRA.EXTPROCESSORID

    INNER JOIN VP_EMPLOYEEV42 EMP

        ON PRA.EMPLOYEEID = EMP.EMPLOYEEID

    WHERE PR.EXTPROCESSORDSC = 'Fixed Percent Allocation Employee Rule'

    AND to_char(to_date(:EFFECTIVE_DATE, 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mm')

    BETWEEN EFFECTIVEDTM AND EXPIRATIONDTM

    AND EMP.PERSONNUM = ':EMPLOYEE_ID'

    AND ROWNUM = 1;

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,728 Red Diamond
    edited Oct 11, 2019 9:03AM

    Are you saying EFFECTIVEDTM and EXPIRATIONDTM datatype is VARCHAR2? If so, you have serious design flaw. They should be defined as DATE.

    Anyway, look at:

    AND to_char(to_date(:EFFECTIVE_DATE, 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mm')

    Format mm is month while you are looking for mi - minutes:

    AND to_char(to_date(:EFFECTIVE_DATE, 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mi')

    Another issue is hh which is 12 hour clock with AM/PM. Based on EFFECTIVEDTM and EXPIRATIONDTM values you need 24 hour clock:

    AND to_char(to_date(:EFFECTIVE_DATE, 'dd/mm/yyyy'), 'yyyy-mm-dd hh24:mi')

    SY.

  • mathguy
    mathguy Member Posts: 10,677 Blue Diamond
    edited Oct 11, 2019 9:04AM

    I don't understand.

    In the original post you had this:

    select to_char(to_date(':Effective_date', 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mm') from dual

    In the code you posted in Reply 8 you have this:

    to_char(to_date(:EFFECTIVE_DATE, 'dd/mm/yyyy'), 'yyyy-mm-dd hh:mm') BETWEEN EFFECTIVEDTM AND EXPIRATIONDTM

    In the code, :EFFECTIVE_DATE is not enclosed in single quotes, so why did you enclose it in single quotes in your original question?

    On the other hand, in the code you posted you also have

    AND EMP.PERSONNUM = ':EMPLOYEE_ID'

    Here you have the bind variable in single quotes. This is what is throwing the error, not the :EFFECTIVE_DATE thing. Remove the single quotes from around :EMPLOYEE_ID.