Home » SQL & PL/SQL » SQL & PL/SQL » Can put nls format into procedure......
Can put nls format into procedure...... [message #241570] Wed, 30 May 2007 01:19 Go to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member

Can put nls date format into procedure......?
Re: Can put nls format into procedure...... [message #241573 is a reply to message #241570] Wed, 30 May 2007 01:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
yes

If you elaborate more on what you mean, I will on my answer.
Re: Can put nls format into procedure...... [message #241576 is a reply to message #241573] Wed, 30 May 2007 01:29 Go to previous messageGo to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
i want to convert date in yyyy/mm/dd format & after that it should be in date datatype not in character datatype.

like : cast(to_char(sysdate,'yyyy/mm/dd') as date)
its gives error becoz to_char(sysdate,'yyyy/mm/dd') is not a numeric value....

regards,
dinesh
Re: Can put nls format into procedure...... [message #241579 is a reply to message #241576] Wed, 30 May 2007 01:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
lokhande.dinesh29 wrote on Wed, 30 May 2007 08:29
cast(to_char(sysdate,'yyyy/mm/dd') as date)
This doesn't make sense at all: SYSDATE is already a date. Why would anyone convert it to a VARCHAR2 and back to a DATE? If you want the time portion removed from SYSDATE, try TRUNC(SYSDATE) instead.

MHE
Re: Can put nls format into procedure...... [message #241581 is a reply to message #241579] Wed, 30 May 2007 01:53 Go to previous messageGo to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
suppose in the place of sysdate if i have parameter of character type then how we can do casting?....
Re: Can put nls format into procedure...... [message #241588 is a reply to message #241581] Wed, 30 May 2007 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
to_date.

Regards
Michel
Re: Can put nls format into procedure...... [message #241589 is a reply to message #241581] Wed, 30 May 2007 02:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You'd use TO_DATE( yourstring, yourdateformat, nlsparamters). The last parameter is optional. In fact, the second parameter can be left out too but that way you're depending on the NLS of the database. That's not the brightest idea and it's bound to cause problems one day or another. Anyway, TO_DATE it is.

Edit: I'm glad to see that Michel agrees Very Happy. I should have known that you were faster, Michel.

MHE

[Updated on: Wed, 30 May 2007 02:13]

Report message to a moderator

Re: Can put nls format into procedure...... [message #241602 is a reply to message #241589] Wed, 30 May 2007 02:47 Go to previous messageGo to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
can u tell me the default date format of oracle 10g


thanx for all ur valuable inputs....
Re: Can put nls format into procedure...... [message #241606 is a reply to message #241602] Wed, 30 May 2007 02:55 Go to previous message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The one you have in your parameter file or login.sql if you use SQL*Plus.

Try
select value from nls_[session|instance|database]_parameters where parameter like '%FORMAT';

Regards
Michel

[Updated on: Wed, 30 May 2007 02:55]

Report message to a moderator

Previous Topic: how to reslove table spaces
Next Topic: unable to audit
Goto Forum:
  


Current Time: Wed Dec 07 03:14:28 CST 2016

Total time taken to generate the page: 0.10212 seconds