1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Format mask: Optional???

Discussion in 'SQL PL/SQL' started by monkey, Jun 6, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    It says, format_mask is ment to be optional, but writting this returns an error:

    Code (SQL):
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('17.07.2015.'));
    this is ok:

    Code (SQL):
    INSERT INTO brother(brotherbirthdate) VALUES(to_date('17.07.2015.','dd.mm.yyyy'));
    So, is the last element format_mask at all or not?
    many thanks!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    monkey likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If your date string matches the 'default' format for the session no format string is necessary, otherwise you'll see the errors displayed in the below example:


    Code (SQL):

    SQL> --
    SQL> -- Default date display format
    SQL> --
    SQL> SELECT VALUE
      2  FROM v$nls_parameters
      3  WHERE parameter='NLS_DATE_FORMAT';


    VALUE
    ----------------------------------------------------------------
    DD-MON-RR


    SQL>
    SQL> --
    SQL> -- Return current date
    SQL> --
    SQL> SELECT sysdate FROM dual;


    SYSDATE
    ---------
    06-JUN-14


    SQL>
    SQL> --
    SQL> -- Convert date string to internal date format
    SQL> --
    SQL> -- Date string matches default date format
    SQL> --
    SQL> SELECT to_date('11-JUN-78') FROM dual;


    TO_DATE('
    ---------
    11-JUN-78


    SQL>
    SQL> --
    SQL> -- Date string does NOT match default format
    SQL> --
    SQL> -- First select returns error
    SQL> --
    SQL> select to_date('
    June 17, 1999') from dual;
    select to_date('
    June 17, 1999') from dual
                   *
    ERROR at line 1:
    ORA-01858: a non-numeric character was found where a numeric was expected

    SQL>
    SQL> --
    SQL> -- Date string converted
    SQL> --
    SQL> select to_date('
    June 17, 1999','MONTH dd, RRRR') from dual;


    TO_DATE('

    ---------
    17-JUN-99


    SQL>
    SQL> --
    SQL> -- Change the display format
    SQL> --
    SQL> -- Does NOT affect stored values
    SQL> --
    SQL> ALTER SESSION SET nls_date_format='DD-MM-RRRR HH:MI:SS AM';


    SESSION altered.


    SQL>
    SQL> --
    SQL> -- Return current date
    SQL> --

    SQL> SELECT sysdate FROM dual;


    SYSDATE
    ----------------------
    06-06-2014 09:03:57 AM


    SQL> SELECT to_date('23-12-2004 01:11:11 PM') FROM dual;


    TO_DATE('23-12-200401:
    ----------------------
    23-12-2004 01:11:11 PM


    SQL> select to_date('
    23-12-2004 13:11:11') from dual;
    select to_date('
    23-12-2004 13:11:11') from dual
                   *
    ERROR at line 1:
    ORA-01849: hour must be between 1 and 12

    SQL>
    SQL> --
    SQL> -- Change the display format
    SQL> --
    SQL> alter session set nls_date_format='
    DD-MON-RRRR HH24:MI:SS';


    Session altered.


    SQL>
    SQL> --
    SQL> -- Return current date
    SQL> --
    SQL> select sysdate from dual;


    SYSDATE
    --------------------
    06-JUN-2014 09:03:57


    SQL> select to_date('
    23-DEC-2004 01:11:11 PM') from dual;
    select to_date('
    23-DEC-2004 01:11:11 PM') from dual
                   *
    ERROR at line 1:
    ORA-01830: date format picture ends before converting entire input string

    SQL> select to_date('
    23-DEC-2004 13:11:11') from dual;


    TO_DATE('
    23-DEC-2004
    --------------------
    23-DEC-2004 13:11:11


    SQL>
    SQL> --
    SQL> -- Change display format
    SQL> --
    SQL> -- Display date in words
    SQL> --
    SQL> ALTER SESSION SET nls_date_format='fmDay," the "ddth" of "FMMonth, RRRR';


    SESSION altered.


    SQL>
    SQL> --
    SQL> -- Return current date
    SQL> --
    SQL> SELECT sysdate-5 FROM dual;


    SYSDATE-5
    --------------------------------------
    Sunday, the 1st OF June     , 2014


    SQL>
     
     
    monkey likes this.
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Everything you type in on a SQL line is text. The value 12345 in a SQL statement is text from Oracle's perspective. When it stores that value in a NUMBER field, there is not a section of the hard disk that will contain the ASCII characters 12345. Instead there will be a byte value that equals 12345.

    Given the following commands:
    Code (Text):
    CREATE TABLE numtable (col1 NUMBER);
    INSERT INTO numtable VALUES (12345);
    Oracle will perform an implicit conversion of 12345 into the storage format that Oracle uses to hold numeric data in a table. If I perform the following command, Oracle will perform an explicit conversion to the numeric format:
    Code (Text):
    INSERT INTO numtable VALUES (TO_NUMBER(67890));
    Oracle is so good at performing implicit conversions between data types that most people new to Oracle don't realize that a conversion is even being performed. The implicit conversion 'failures' are most often seen with dates because there are dozens of different ways to write a date, and Oracle can only perform implicit conversion on one format at any given time (the NLS_DATE_FORMAT as noted earlier).
     
    monkey likes this.