OraFAQ Forum: SQL & PL/SQL » date column issue

Home » SQL & PL/SQL » SQL & PL/SQL » date column issue (oracle 11.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

date column issue [message #611967]

Thu, 10 April 2014 08:14

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Dear all,

I want to insert the date data into column from the cursor

for dt in (select to_date('31-dec-1989', 'dd-mon-yyyy') + level stdt, LEVEL lvl from dual connect by level < 10
loop

insert into table (CALENDAR_DATE) values(TO_date(dt.stdt, 'DD-MON-YYYY HH24:MI:SS'));

COMMIT;

end loop;
end;
/

But this insert the data 09-JAN-90 instead 31-dec-0090

how to get 1990 instead 0090

Report message to a moderator

Re: date column issue [message #611970 is a reply to message #611967]

Thu, 10 April 2014 08:22

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

I think you are confusing storage of the data with presentation of the data.
What do you get from

select to_char(yourdatecolumn,'dd-Mon-yyyy') from yourtable;

See see: http://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Report message to a moderator

Re: date column issue [message #611971 is a reply to message #611967]

Thu, 10 April 2014 08:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>how to get 1990 instead 0090
NEVER use TO_DATE on DATE datatype; which I assume "DT.STDT" is DATE datatype.

Report message to a moderator

Re: date column issue [message #611972 is a reply to message #611971]

Thu, 10 April 2014 08:27

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

If i don't use and insert
insert into table (CALENDAR_DATE) values(dt.stdt );
then also it is inserting the data 09-JAN-0090 instead 09-JAN-1990

Report message to a moderator

Re: date column issue [message #611973 is a reply to message #611972]

Thu, 10 April 2014 08:29

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

guddu_12 wrote on Thu, 10 April 2014 08:27

If i don't use and insert
insert into table (CALENDAR_DATE) values(dt.stdt );
then also it is inserting the data 09-JAN-0090 instead 09-JAN-1990

Because you are forcing oracle to use implicit data type conversions based on some setting of NLS_DATE_FORMAT. It is all explained in the link I gave you, above.

Report message to a moderator

Re: date column issue [message #611974 is a reply to message #611973]

Thu, 10 April 2014 08:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: date column issue [message #611975 is a reply to message #611974]

Thu, 10 April 2014 08:47

VladGab
Messages: 11
Registered: March 2014

Junior Member

Try to replace your mask 'dd-Mon-yyyy' to 'dd-Mon-RRRR'

Report message to a moderator

Re: date column issue [message #611976 is a reply to message #611975]

Thu, 10 April 2014 09:03

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Assuming calendar_date is a date all that is needed is to remove the to_Date in the insert statement as Blackswan already suggested. If that doesn't work then the OP isn't running the code they say they are.

Report message to a moderator

Re: date column issue [message #611977 is a reply to message #611976]

Thu, 10 April 2014 09:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>date column issue (oracle 11.3)

please post complete results from SQL below

SELECT * FROM V$VERSION;

Report message to a moderator

Re: date column issue [message #611979 is a reply to message #611977]

Thu, 10 April 2014 09:14

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Thanks for the help,

there was a silly mistake that i had to convert the date to char before inserting them .

Report message to a moderator

Re: date column issue [message #611980 is a reply to message #611979]

Thu, 10 April 2014 09:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

12. If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.

Report message to a moderator

Re: date column issue [message #611985 is a reply to message #611979]

Thu, 10 April 2014 09:51

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

guddu_12 wrote on Thu, 10 April 2014 09:14

Thanks for the help,

there was a silly mistake that i had to convert the date to char before inserting them .

So is your "date" column defined as a varchar? If so, that is a 'fail'. Storing dates in anything but a DATE data type is a huge mistake and will come back to bite you. If you are doing that, I'll start a pool on how long it will take you to come back with a problem that resulted from it.

Report message to a moderator

Re: date column issue [message #611995 is a reply to message #611967]

Thu, 10 April 2014 12:35

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

guddu_12 wrote on Thu, 10 April 2014 09:14

for dt in (select to_date('31-dec-1989', 'dd-mon-yyyy') + level stdt, LEVEL lvl from dual connect by level < 10
loop

insert into table (CALENDAR_DATE) values(TO_date(dt.stdt, 'DD-MON-YYYY HH24:MI:SS'));

COMMIT;

end loop;
end;
/

Maybe you want to show us the code you actually executed, because this one will not for at least 2 reasons (ie. unbalanced parenthesis and no begin/end code).

Report message to a moderator

Previous Topic:	Generating PDF files at oracle 10g server
Next Topic:	oracle store procedure execution time takes too long

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:07:37 CDT 2024