5 Replies Latest reply: Oct 24, 2012 1:01 PM by Irian RSS

    To_Date()

    970400
      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
          So what error are you getting?
          • 2. Re: To_Date()
            Irian
            This question has nothing to do with SQLDeveloper and should go to the SQL and PL/SQL forum

            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
              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
                >
                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
                  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.