Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Format mask for DATETIME

    Hi

    I have a datetime entry like:

    Code:
    2013-02-24 19:00+04:00
    How can I write the format mask for this?

    E.g.,
    Code:
    SELECT
    TO_DATE('2013-02-24 19:00+04:00','YYYY-MM-DD HH24:MI ????') FROM DUAL
    Thanks
    Shajju

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DATE datatype does NOT contain any Timezone Detail; only TIMESTAMP datatype does
    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
    Aug 2008
    Posts
    464
    Thanks. I used
    SELECT
    TO_TIMESTAMP_TZ ('2013-02-24 14:30+04:00','YYYY-MM-DD HH24:MI TZH:TZM') FROM DUAL

    And this returns:

    2/24/2013 2:30:00:000000000 PM + 04:00

    Can I get rid of the extra zeros?

    Regards
    Last edited by shajju; 02-26-13 at 01:18.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    In other words, can I just select the datetime bit:

    Code:
    2013-02-24 19:00
    from the string

    Code:
    2013-02-24 14:30+04:00
    and load it into column with data type date/timestamp?

    Regards

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >In other words, can I just select the datetime bit:
    I give up.
    Can you?
    How do you just get part of a string?
    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
    Aug 2008
    Posts
    464
    What I meant was I can get part of the string using substr but then I want to convert it to DATE format at the same time so I can load it into a DATE data type column.

    I figured it out. Thanks for the hint.

    Code:
    select TO_DATE(SUBSTR('2013-02-24 19:00+04:00',0,INSTR('2013-02-24 19:00+04:00','+')-1),'YYYY-MM-DD HH:MI') FROM DUAL
    Last edited by shajju; 02-27-13 at 01:37.

Posting Permissions

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