Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Posts
    50

    Unanswered: Can't insert NULL value in DATE data type.

    Sorry... This must be a very basic one. Why can't I insert a NULL value in a date, not having a NOT NULL constraint in that field?

    CREATE TABLE Example(
    name VARCHAR(30),
    regist DATE,
    PRIMARY KEY(name)
    );

    INSERT INTO Example VALUES ('anyname','NULL');

    ORA-01841: (full) year must be between -4713 and +9999, and not be 0


    Thanks for your attention!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    You should not have '' around the NULL.

    INSERT INTO Example VALUES ('anyname',NULL);

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Dec 2003
    Posts
    50
    Thanks for the tip and for the very fast answer!

  4. #4
    Join Date
    Dec 2003
    Posts
    50

    Another doubt relative to the date data type

    I would like just to put the year value when I don't know the exact date but I get an

    ORA-01861: literal does not match format string

    What is the best sollution for this? Can I put just the year information in the date field or do I have to create a field NUMBER(4) to put the year value in the cases where I don't know the exact date?

    Thanks for the attention, again...
    Last edited by rpOliveira; 12-20-03 at 13:14.

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Yes, if you're going to be storing just the year portion of a date, a column specifically for the year might be appropriate.

    The problem comes when you start to do queries across tables which contain real dates - this could cause you to be doing lots of to_date( year_column ) or to_number( to_char( datecolumn, 'YYYY')).

    If all of the dates will be stored as year only, you could consider just having the 1st Jan on all of them. I couldn't say what the performance differences would be between the two approaches.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Dec 2003
    Posts
    50
    Yes, but I'm just storing the year portion of a date when I don't know the complete date (information for the birth date of artists). So, in some cases I wil have a complete date and in others I will just have an year to store. Regarding your words, I think a sollution is to have a DATE field to store the dates and a year field for the other cases.


    if (date == NULL)
    then birth_date = year;
    else
    birth_date = date;

    Thanks,

    Ricardo

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    So you'd have two columns, one just for the year and one for the full date.

    For a known birthdate you would populate both, and for a year of birth only, just populate the year column.

    This gives you the option to find all people born in a year, or on a specific date.

    The other option would be to store the date as YYYYMMDD string.

    This is quite acceptable and allows you to do both in the one column. You would need to do your own date validation though.

    Artists born in 1970 ... where dob like '1970%'
    Artists born in March 1970 ... where dob like '197003%'
    Artists born on 1st March 1970 ... where dob = '19700301'

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Dec 2003
    Posts
    50
    OK! Thanks a lot for your answers!

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    No worries mate,

    Just note the disclaimer on my posts and note also that it's past pub closing time in England. Best of luck however you choose to proceed :-)

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  10. #10
    Join Date
    Dec 2003
    Posts
    1
    Originally posted by billm
    Hi,

    You should not have '' around the NULL.

    INSERT INTO Example VALUES ('anyname',NULL);

    Hth
    Bill
    SQL> INSERT INTO Example VALUES ('anyname',null);

    1 row created.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by billm
    So you'd have two columns, one just for the year and one for the full date.

    For a known birthdate you would populate both, and for a year of birth only, just populate the year column.
    then you'd need another column for the month, if you knew the year and month but not the day, and you could carry it to absurb lengths and have columns for year, month, day, hour and minute, if you did not know the seconds

    this issue (storing the year but not the month and day if you don't know them) is the rationale behind the ridiculous (in my opinion) decision on the part of mysql developers to allow invalid dates:
    To make the date checking 'fast', MySQL only checks that the month is in the range of 0-12 and the day is in the range of 0-31. The above ranges are defined this way because MySQL allows you to store, in a DATE or DATETIME column, dates where the day or month-day is zero. This is extremely useful for applications that need to store a birth-date for which you don't know the exact date. In this case you simply store the date like 1999-00-00 or 1999-01-00.
    -- http://www.mysql.com/doc/en/Date_and_time_types.html
    insanity


    rudy
    http://r937.com/

  12. #12
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    INSERT INTO Example(name) VALUES ('anyname');
    SATHISH .

Posting Permissions

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