This discussion is archived
5 Replies Latest reply: Oct 24, 2012 11:01 AM by Irian RSS

To_Date()

970400 Newbie
Currently Being Moderated
Dear Fellows,
i want to save 10/24/2012 to vdatefrom variable i have below line in my Script


vdatefrom := TO_DATE ('01-MON-2012 00:00:00', 'DD-MON-RRRR HH24:MI:SS');

Please help me.
  • 1. Re: To_Date()
    602881 Newbie
    Currently Being Moderated
    So what error are you getting?
  • 2. Re: To_Date()
    Irian Pro
    Currently Being Moderated
    This question has nothing to do with SQLDeveloper and should go to the SQL and PL/SQL forum

    SQL and PL/SQL


    in your case you probably want to change the NLS dependent "MON" date formatting option with the more generic "MM" wich allows you to use numbers instead of month short names.
    vdatefrom := TO_DATE ('10-24-2012 00:00:00', 'MM-DD-RRRR HH24:MI:SS');
    EDIT - fixed typo on date formatting

    Edited by: Irian on 24-ott-2012 16.44
  • 3. Re: To_Date()
    602881 Newbie
    Currently Being Moderated
    Good point on the forum

    For completeness

    TO_DATE ('01-MON-2012 00:00:00', 'DD-MON-RRRR HH24:MI:SS') -- In your input data MON is not a valid month abbreviation
    TO_DATE ('10-24-2012 00:00:00', 'DD-MM-RRRR HH24:MI:SS') -- Your input data looks more like the MM-DD format than DD-MM
  • 4. Re: To_Date()
    rp0428 Guru
    Currently Being Moderated
    >
    in your case you probably want to change the NLS dependent "MON" date formatting option with the more generic "MM" wich allows you to use numbers instead of month short names.
    >
    Utter nonsense. As Ross already said OPs problem is that 'MON' is NOT a valid month
    vdatefrom := TO_DATE ('01-MON-2012 00:00:00', 'DD-MON-RRRR HH24:MI:SS');
    The value '01-MON-2012' is the first day of the imaginary month whose abbreviation is 'MON'?

    When string literals are provided for the date value you can use any valid date format you want as long as the string literal represents a valid date for the format provided.
  • 5. Re: To_Date()
    Irian Pro
    Currently Being Moderated
    You are right 'MON' is not a valid month, I was assuming it to be just a wrong example written in a hurry,
    the point i was trying to make is that if you use 'MON' as date format string you are then tied to the NLS setting since the month abbreviation changes.

    so writing
    SELECT TO_DATE ('01-JAN-2012 00:00:00', 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;
    will work in an English or American locale but not in an Italian one, so i personally prefer to stick with numeric formatting at least for data input.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points