dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: pl/sql field formatting

  1. #1
    Join Date
    Nov 2005
    Posts
    9

    pl/sql field formatting

    I was wondering if there is a way that you can have a client enter in a varchar field a date in the format of 20051101 only. Is there something in the table column that can be set, like the default or something like that. If this format is not entered this way then my procedure will say it cannot recognize this as a date. I tried making it a date field. Some how this was the only way I could get it to work.

    please help.

    thanks in advance!

  2. #2
    Join Date
    Jun 2005
    Posts
    1,255

    Smile Re: pl/sql field formatting

    You can use a trigger "BEFORE INSERT OR UPDATE" of your field.
    Or you can add a "CHECK CONSTRAINT", e.g.:
    Code:
    CREATE TABLE foo
    (fielddate VARCHAR2(8)
     CONSTRAINT check_dateyyyymmdd
      CHECK (    (TO_NUMBER(SUBSTR(fielddate, 1, 4)) BETWEEN 1900 AND 2099)
             AND (TO_NUMBER(SUBSTR(fielddate, 5, 2)) BETWEEN 1 AND 12)
             AND (TO_NUMBER(SUBSTR(fielddate, 7, 2)) BETWEEN 1 AND 31))
     fieldother .....
    )

  3. #3
    Join Date
    Nov 2005
    Posts
    9

    Re: pl/sql field formatting

    I think I will try the constraint with the table. That is more of what I am looking for. So that will be in the table when it is created, right? So, when they enter a date in that field of the table, they will only be able to enter, let's say, 20051101. thanks!

  4. #4
    Join Date
    Jun 2005
    Posts
    1,255

    Smile Re: pl/sql field formatting

    The constraint is added when the table is created, or, theoretically, the constraint can be added later with
    Code:
    ALTER TABLE     table
    MODIFY          column datatype CONSTRAINT column-constraint;
    I suppose, it is:
    Code:
    ALTER TABLE foo
    MODIFY fielddate VARCHAR2(8)
     CONSTRAINT check_dateyyyymmdd
      CHECK (    (TO_NUMBER(SUBSTR(fielddate, 1, 4)) BETWEEN 1900 AND 2099)
             AND (TO_NUMBER(SUBSTR(fielddate, 5, 2)) BETWEEN 1 AND 12)
             AND (TO_NUMBER(SUBSTR(fielddate, 7, 2)) BETWEEN 1 AND 31));

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)