Date/Time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Date/Time

  1. #1
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    I have this statement:

    insert into dummy (dummy_date) VALUES (to_date('10:20','HH:MM'));

    Then I run the following statemet:

    SELECT TO_CHAR(dummy_date,'DD-MM-YYYY HH:MI:SS') FROM DUMMY;
    It returns:
    01-04-2001 10:20:00

    Please note that since I didn't insert the date, it is storing 1st of Current month. I want to make it 1st of January by default. How can I do that?
    Agasimani
    OCP(10g/9i/8i/8)

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The behavior you see is expected and documented. From Oracle8i Application Developer's Guide:

    quote
    In a time-only entry, the date portion defaults to the first day of the current month.
    /quote

    To truncate the date to the first day of the current year (with inserted time portion included) you have to use some additional date functions. For example, with the use of the additional TO_CHAR function you can do the following:
    INSERT INTO dummy (dummy_date) VALUES
    (TO_DATE(TO_CHAR(TRUNC(SYSDATE,'YYYY'),'DD-MM-YYYY')||'10:20','DD-MM-YYYYHH:MI'));

    Or with strictly using only standard date functions, without any additional to_char/to_date conversion you could use the following:

    INSERT INTO dummy (dummy_date) VALUES
    (ADD_MONTHS(TO_DATE('10:20','HH:MI'), -TRUNC(MONTHS_BETWEEN(SYSDATE, TRUNC(SYSDATE,'YYYY')))));

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width