Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2

    Unanswered: datatype for time in oracle

    Hi,

    which datatype we used to store the time data in oracle...if i am using the timestamp its storing the time with date also. i just want to store time HH24:MIS in this way...Please suggest..i am using sqlloader to load the data.

    oracle version... 11gr2
    when i loaded with datatype timestamp...its loading the like this....

    01-MAY-14 05.29.09.000000000 PM
    01-MAY-14 02.15.02.000000000 PM
    01-MAY-14 05.21.22.000000000 PM
    01-MAY-14 10.30.15.000000000 AM
    01-MAY-14 05.18.14.000000000 PM
    01-MAY-14 12.18.06.000000000 PM
    01-MAY-14 05.29.58.000000000 PM
    01-MAY-14 02.15.52.000000000 PM
    01-MAY-14 05.22.03.000000000 PM

    which i dont want...i am trying to store the data in this way

    05.29.09
    02.15.02
    05.21.22
    10.30.15

    Please advise....thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails, Read The Fine Manual.

    Data Types
    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
    Feb 2013
    Posts
    39
    Provided Answers: 2
    here i am asking some logic or the way to deal it with the current requirement. i also some mind to direct here and there...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >here i am asking some logic or the way to deal it with the current requirement.
    I don't understand what the requirement is.
    what exactly do these time values represent?
    what is the source of these data values?
    Why can't you use DATE datatype & just ignore the day component?
    Why is INTERVAL datatype not acceptable?
    exactly how will these values be used by the application?
    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.

  5. #5
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2
    i have fixed length data in a txt file. and its has two fields d_date which contains the date only data(mm/dd/yy) and the other one is d_time which contain the time data(hh:mi:ss). the requirement is that i have to use for d_date the date datatype and for d_time i have to use the timestamp datatype...when i am loading as per the requirement i am get loading in d_time as a full date and time data....which i dont want. i just want to load the time portion.....thanks


    d_date date
    d_time timestamp

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why?
    you can extract the time element as required
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2
    i have the data in the txt file in this format....

    11/12/0817:29:52
    11/16/0914:15:45
    11/16/1017:21:59
    11/15/1110:30:58
    11/15/1217:18:51
    11/18/1312:18:39

    using control file.....

    B_DATE POSITION (1:8) DATE "MM/DD/YY",
    B_TIME POSITION (9:16) TIMESTAMP,

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    data should be loaded into a single DATE column!

    By the way there are two differences between DATE & TIIMESTAMP datatypes
    1) TIMESTAMP can contain TimeZone information
    2) TIMESTAMP contains fractions of a second resolution

    DATE datatype contains neither timezone nor fraction of second

    in otherwords TIMESTAMP is not the correct datatype from your data.
    Last edited by anacedent; 05-28-14 at 14:31.
    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.

  9. #9
    Join Date
    Feb 2013
    Posts
    39
    Provided Answers: 2
    Thanks Ana..."data should be loaded into a single date column..." this make sense to me....

Posting Permissions

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