Results 1 to 7 of 7

Thread: format datetime

  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: format datetime

    Hi Guys,

    Actually, im jst having a small issue fixing the time in the date to a particular time.

    For instance, i have a date stored in a column without time

    ie: 20-11-08

    we can use the to_char to format the date and stuff, but in this case, i wish to fix the time to 12:00:00 am.

    this is needed, because when im doing a greater than value comparison,
    i also want it to be > 20-11-08 12:00:00am , im just having trouble hardcoding the time

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >For instance, i have a date stored in a column without time
    I doubt that this is true. DATE datatype always contains time portion.

    >we can use the to_char to format the date and stuff,
    The "stuff" is the time portion.

    >i also want it to be > 20-11-08 12:00:00am ,
    Can be obtained using TRUNC() function.

    >im just having trouble hardcoding the time
    Use CUT & PASTE to show us what you've tried & what were the results.

    http://download.oracle.com/docs/cd/B...htm#sthref2296
    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
    Nov 2008
    Posts
    26
    [QUOTE=anacedent]>For instance, i have a date stored in a column without time
    I doubt that this is true. DATE datatype always contains time portion.

    >i also want it to be > 20-11-08 12:00:00am ,
    Can be obtained using TRUNC() function.


    thanks for your reply

    i was basically trying to add time to a date using date + (1/24) * 12

    Im assuming this will add 12 hours to the time portion.

    Lets say i store today's date in a column using
    trunc(sysdate), does this mean im storing the time portion as 12:00:00 am?

    Because i have table stored in our database.

    When i select the date out using the following query

    select to_char(revision_date,'dd-mm-yyyy hh24:mi') from std_val

    im getting the results as

    20-11-2008 00:00 ? does the time portion means 12:00:00 am?

    or shall i change to format to 'dd-mm-yyyy hh:mi:ssam'
    Last edited by ajitpal.s; 12-21-08 at 05:27.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ajitpal.s
    20-11-2008 00:00 ? does the time portion means 12:00:00 am?
    I can't get used to the 12 hour thing with times, but if 12am is midnight, then it does mean exactly that.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends.
    Does a day go from 00:00:01 to 24:00:00 or 00:00:00 to 23:59:59?
    Both contain 24*60*60 seconds.
    So it depends upon what value is used to represent the initial time for a day.
    Do you start counting the seconds at zero or at one?
    Pick your convention & stay consistent!
    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
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    It depends.
    Does a day go from 00:00:01 to 24:00:00 or 00:00:00 to 23:59:59?
    Both contain 24*60*60 seconds
    I think the "standard" is pretty clear in that, that there is no such thing as 24:00:00.
    The day goes from 00:00:00 to 23:59:59.

    Sometimes 24:00 is simply used to make intervals more "readable" (e.g. open from 00:00 to 24:00, because 00:00 - 00:00 would look quite confusing ) but to my knowledge it does not exist as a "real value" - at least not in Oracle:

    SELECT to_date('2008-01-01 23:59:59', 'yyyy-mm-dd HH24:mi:ss') + interval '1' second
    FROM dual;

    Returns: 2008-01-02 00:00:00

  7. #7
    Join Date
    Dec 2008
    Posts
    59

Posting Permissions

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