OraFAQ Forum: SQL & PL/SQL » Can put nls format into procedure......

Home » SQL & PL/SQL » SQL & PL/SQL » Can put nls format into procedure......

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

Can put nls format into procedure...... [message #241570]

Wed, 30 May 2007 01:19

lokhande.dinesh29
Messages: 36
Registered: May 2007

Member

Can put nls date format into procedure......?

Report message to a moderator

Re: Can put nls format into procedure...... [message #241573 is a reply to message #241570]

Wed, 30 May 2007 01:21

Frank
Messages: 7901
Registered: March 2000

Senior Member

yes

If you elaborate more on what you mean, I will on my answer.

Report message to a moderator

Re: Can put nls format into procedure...... [message #241576 is a reply to message #241573]

Wed, 30 May 2007 01:29

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

Report message to a moderator

Re: Can put nls format into procedure...... [message #241579 is a reply to message #241576]

Wed, 30 May 2007 01:38

Maaher
Messages: 7065
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

Report message to a moderator

Re: Can put nls format into procedure...... [message #241581 is a reply to message #241579]

Wed, 30 May 2007 01:53

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?....

Report message to a moderator

Re: Can put nls format into procedure...... [message #241588 is a reply to message #241581]

Wed, 30 May 2007 02:07

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

to_date.

Regards
Michel

Report message to a moderator

Re: Can put nls format into procedure...... [message #241589 is a reply to message #241581]

Wed, 30 May 2007 02:11

Maaher
Messages: 7065
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

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....

Report message to a moderator

Re: Can put nls format into procedure...... [message #241606 is a reply to message #241602]

Wed, 30 May 2007 02:55

Michel Cadot
Messages: 68719
Registered: March 2007
Location: Saint-Maur, France, https...

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 19:35:22 CST 2024