-
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)
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|