Re: help with date insertions

Yousaf wrote:

> Dear all,
>
> I have written a post earlier about having trouble with the dates in Oracle,
> I have followed what I was told.
>
> I am inserting a record via ASP, and in the query I insert the date with the
> TO_DATE function, here is what I am doing:
>
> INSERT INTO RISK(RISK_NO, USER_ID, DESCRIPTION, RAISED_BY, LOG_DATE, IMPACT,
> OWNER, RESOLUTION, STATUS, EXPECTED_CLOSURE_DATE, CLOSED_DATE,
> ADDITIONAL_OWNER, OTHER_OWNER) VALUES(RISK_NOSeq.nextval, 'DUFFYA' ,'this
> is a test','MOOREK23', SYSDATE, 'H', 'HILLJE', '-','O',
> TO_DATE('04-MAR-2003', 'DD/MON/YYYY'), TO_DATE('09-AUG-2015','DD/MON/YYYY')
> ,'N', '-')
>
> After performing this insert via ASP, using SQL Plus I check the date:
>
> SQL> select EXPECTED_CLOSURE_DATE from risk where risk_no = 71;
>
> EXPECTED_
> ---------
> 04-MAR-03
>
> Which is wrong as clearly in the TO_DATE function I want it to store in YYYY
> format, so it should appear as 2003, not 03.
>
> Also when I retrieve the same record in ASP and display it on the screen it
> shows me the same date as following:
>
> 3/4/2003
>
>
> Could anyone tell me what is going on here???
>
> Thanking you in advance.

What is going on here is a question of display format. Oracle, by default displays years as two digits. In fact ALL Oracle dates contain day, month, four digit year, hour, minute, and second.

Here's the query you should be writing:

select TO_CHAR(EXPECTED_CLOSURE_DATE, 'DD-MON-YYYY HH:MI:SS') FROM risk
WHERE risk_no = 71;

to see what you have inserted.

Personally I'd never write an insert statement in ASP or any other front-end tool. Is is inefficient and just makes the front-end fatter. Far better to build a stored procedure that performs the insert, pass it the parameters, and have it return a confirming message indicating sucess or failure.

But there is nothing wrong with what you've done.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)