Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Unanswered: Confusion About Date Data Type

    I know this is a commonly asked question for Oracle but I just can't seem to find any documentation that helps me understand how to enter in actual date entries in 11g. My database has a column named 'USERS_DOB' which has a 'DATE' data type:

    Code:
    SQL> describe CMENNENS.USERS;
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     USERS_ID				   NOT NULL NUMBER(4)
     USERS_NAME				   NOT NULL VARCHAR2(100)
     USERS_EMAIL				   NOT NULL VARCHAR2(100)
     USERS_DOB					    DATE
    Now I want to enter a users DoB which is '1979-02-19' but it seems that Oracle's 'data' format also requires a time 00:00:00 am/pm. Can someone tell me if I MUST enter a time with the date format or is there a way I can simply use a common date like 2-19-1979?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >if I MUST enter a time with the date format or is there a way I can simply use a common date like 2-19-1979?


    TIME component is NOT required

    what date is below

    10-11-12
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    I'm guessing you're trying to prove a point in regards to my date syntax not being correct and how it can be easily confused, right? Nov-10-2012?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    Can someone tell me if I MUST enter a time with the date
    No you don't have to:
    Code:
    select DATE '1979-02-19' from dual
    is perfectly valid. The part DATE '1979-02-19' is a date literal and does not carry a time (or to be precise: it's time will be 00:00:00). If you prefer to write your dates in a different format, you have to use the to_date() function:
    Code:
    select to_date('19-07-1979', 'DD-MM-YYYY') from dual;
    For more details see the manual:
    TO_DATE
    Format Models
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    as a general rule when TIME component is NOT provided, it defaults to "00:00:00" (midnight)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Post

    @CarlosinFL
    A little more just for clarification on how Oracle's DATE datatype works.
    The Oracle DATE datatype is actually storing date and time always. People often get this confused because of the IDE that they are using which will format the date value depending on certain custom rules.

    I like to use SQLPLUS to demonstrate this because we can alter the output format very easily. Take a look at the following:
    Code:
    dayneo@RMSD> select * from nls_session_parameters
      2  where parameter = 'NLS_DATE_FORMAT';
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_DATE_FORMAT                DD-MON-RR
    
    dayneo@RMSD> select sysdate from dual;
    
    SYSDATE
    ---------
    13-JUL-12
    
    dayneo@RMSD> alter session set nls_date_format="DD-MON-YYYY HH24:MI:SS";
    
    Session altered.
    
    dayneo@RMSD> select * from nls_session_parameters
      2  where parameter = 'NLS_DATE_FORMAT';
    
    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_DATE_FORMAT                DD-MON-YYYY HH24:MI:SS
    
    dayneo@RMSD> select sysdate from dual;
    
    SYSDATE
    --------------------
    13-JUL-2012 13:58:21
    
    dayneo@RMSD>
    1. In the first statement, I check what the current Oracle NLS date format is. The NLS date format is the format that Oracle will use when implicitly converting a date to a string and visa versa. Note that the format is currently only set to display the date part of a DATE datatype.
    2. In the second statement, I select sysdate and SQLPLUS returns the current DATE formatted as per the NLS date format parameter.
    3. I then alter my NLS date format parameter to include a time component.
    4. Selecting the sysdate again returns both a date and time.

    This simple demonstration shows that a date always contains both a date and time component and that it is purely the conversion of a date to a string for presentation purposes that changes the perceived format.

Posting Permissions

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