OraFAQ Forum: SQL & PL/SQL » string to date

Home » SQL & PL/SQL » SQL & PL/SQL » string to date (Oracle 11g)

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

string to date [message #600033]

Thu, 31 October 2013 01:32

oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI

Member

I want to convert the follow string to date:
2013-12-04 11:35:54.89

Report message to a moderator

Re: string to date [message #600034 is a reply to message #600033]

Thu, 31 October 2013 01:36

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions203.htm#SQLRF51886

Report message to a moderator

Re: string to date [message #600035 is a reply to message #600033]

Thu, 31 October 2013 01:40

oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI

Member

I have tried like TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),'YYYY-MON-DD HH24:MI:SS.FF2').

Is this correct?or any other shortest way is there?

Report message to a moderator

Re: string to date [message #600037 is a reply to message #600035]

Thu, 31 October 2013 01:41

oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI

Member

Expected result will be '2013-DEC-04 11:35:54.89'

Report message to a moderator

Re: string to date [message #600038 is a reply to message #600035]

Thu, 31 October 2013 01:41

OraFerro
Messages: 433
Registered: July 2011

Senior Member

Hi there,

several answers exist over the net:

http://stackoverflow.com/questions/1758219/string-to-date-in-oracle-with-milliseconds

Report message to a moderator

Re: string to date [message #600041 is a reply to message #600037]

Thu, 31 October 2013 02:00

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

Senior Member
Account Moderator

siraj.accet@gmail.com wrote on Thu, 31 October 2013 07:41

Expected result will be '2013-DEC-04 11:35:54.89'

You have to add the date language:

SQL> select TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),
  2                 'YYYY-MON-DD HH24:MI:SS.FF2',
  3                 'nls_date_language=american')
  4  from dual;
TO_CHAR(TO_TIMESTAMP('2
-----------------------
2013-DEC-04 11:35:54.89

Report message to a moderator

Re: string to date [message #600043 is a reply to message #600041]

Thu, 31 October 2013 02:08

oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI

Member

Hi cadot,
Thanks.But my server NLS_LANGUAGE is AMERICAN only.Do i need to mention Nls_language in query itself?

Report message to a moderator

Re: string to date [message #600057 is a reply to message #600043]

Thu, 31 October 2013 05:38

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

NLS settings are client side, not server side settings. Assume your application issues:

TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),'YYYY-MON-DD HH24:MI:SS.FF2')

Now, result will show MON as DEC when your application is run by client in the US:

SQL> alter session set nls_language=american;

Session altered.

SQL> select TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),'YYYY-MON-DD HH24:MI:SS.FF2') from dual;

TO_CHAR(TO_TIMESTAMP('2
-----------------------
2013-DEC-04 11:35:54.89

SQL>

However, when your application is run by client in Turkey MON will show up as ARA (december is aralik in turkish):

SQL> alter session set nls_language=turkish;

Session altered.

SQL> select TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),'YYYY-MON-DD HH24:MI:SS.FF2') from dual;

TO_CHAR(TO_TIMESTAMP('2
-----------------------
2013-ARA-04 11:35:54.89

Now most of the time this is a good thing since you do want your clients to see data the way clients are accustomed to. But if, for example, you want application users to see dates in same language regardless of client side settings you could use that tghird parameter in TO_CHAR function. Now both client in Turkey and in the US (and anywhere else) will see:

SQL> alter session set nls_language=turkish;

Session altered.

SQL> select TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),'YYYY-MON-DD HH24:MI:SS.FF2','NLS_DATE_LANGUAGE=AMERICAN') from dual;

TO_CHAR(TO_TIMESTAMP('2
-----------------------
2013-DEC-04 11:35:54.89

SQL> alter session set nls_language=american;

Session altered.

SQL> select TO_CHAR(TO_TIMESTAMP('2013-12-04 11:35:54.89','YYYY-MM-DD HH24:MI:SS.FF2'),'YYYY-MON-DD HH24:MI:SS.FF2','NLS_DATE_LANGUAGE=AMERICAN') from dual;

TO_CHAR(TO_TIMESTAMP('2
-----------------------
2013-DEC-04 11:35:54.89

SQL>

SY.

Report message to a moderator

Previous Topic:	convert local time to UTC
Next Topic:	Pivot query: grouping pivot field, using query for IN clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 13 00:27:41 CDT 2025