Home » SQL & PL/SQL » SQL & PL/SQL » Convert to date (Oracle 11g , Win XP)
Convert to date [message #632051] Fri, 23 January 2015 12:14 Go to next message
rajivn786
Messages: 161
Registered: January 2010
Senior Member
Hi,

I have a simple question which I am unable to convert to date...

I have the following stored in varchar2 field ..


2011-07-20T20:57:48.0

Create table a (b varchar2(100));
Insert into a ('2011-07-20T20:57:48.0');


How do I convert this into date format?

The O/p should be

2011-07-20

Report message to a moderator

Re: Convert to date [message #632052 is a reply to message #632051] Fri, 23 January 2015 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Too bad you did NOT actually test what you posted!

SQL> Create table a (b varchar2(100));
Insert into a ('2011-07-20T20:57:48.0');

Table created.

SQL> Insert into a ('2011-07-20T20:57:48.0')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword

Report message to a moderator

Re: Convert to date [message #632060 is a reply to message #632051] Fri, 23 January 2015 12:49 Go to previous messageGo to next message
EdStevens
Messages: 1377
Registered: September 2013
Senior Member
rajivn786 wrote on Fri, 23 January 2015 12:14
Hi,

I have a simple question which I am unable to convert to date...

I have the following stored in varchar2 field ..


Big design mistake. It should have been stored in a DATE field. THen you wouldn't have to convert.


Quote:
How do I convert this into date format?



What have you tried so far. Have you looked at the TO_DATE() function?

Report message to a moderator

Re: Convert to date [message #632065 is a reply to message #632060] Fri, 23 January 2015 13:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/Substr

Report message to a moderator

Re: Convert to date [message #632066 is a reply to message #632051] Fri, 23 January 2015 13:42 Go to previous messageGo to next message
EdStevens
Messages: 1377
Registered: September 2013
Senior Member
rajivn786 wrote on Fri, 23 January 2015 12:14
Hi,


How do I convert this into date format?

The O/p should be

2011-07-20



Are you wanting to convert it to a DATE, or to a STRING with a different format that what it already is?

Report message to a moderator

Re: Convert to date [message #632067 is a reply to message #632065] Fri, 23 January 2015 13:43 Go to previous messageGo to next message
EdStevens
Messages: 1377
Registered: September 2013
Senior Member
BlackSwan wrote on Fri, 23 January 2015 13:30
http://www.orafaq.com/wiki/Substr


I guess it depends on if the OP wants to convert the existing string to a DATE, or to a string of a different format. Hence, my latest response to him - which was triggered by your reference causing me to question if I had mis-read/mis-understood his intent.

Report message to a moderator

Re: Convert to date [message #632668 is a reply to message #632051] Wed, 04 February 2015 02:17 Go to previous messageGo to next message
sridharangoji
Messages: 2
Registered: February 2015
Junior Member
Insert into a values('2011-07-20T20:57:48.0');

Report message to a moderator

Re: Convert to date [message #632670 is a reply to message #632668] Wed, 04 February 2015 02:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
sridharangoji wrote on Wed, 04 February 2015 13:47
Insert into a values('2011-07-20T20:57:48.0');


Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

'2011-07-20T20:57:48.0' is NOT a DATE. It is a string enclosed within single-quotation marks.

Report message to a moderator

Re: Convert to date [message #632671 is a reply to message #632668] Wed, 04 February 2015 02:34 Go to previous messageGo to next message
sridharangoji
Messages: 2
Registered: February 2015
Junior Member
First of all in your query 'values' keyword is missed. So ERROR at line 1:
ORA-00928: missing SELECT keyword occurred.

Second : if you want datatype conversation use To_date function with formatting as following.

Insert into a values(To_date('2011-07-20 20:57:48', 'YYYY-MM-DD HH24:MI:SS'));

Report message to a moderator

Re: Convert to date [message #632672 is a reply to message #632671] Wed, 04 February 2015 02:55 Go to previous message
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
Senior Member
sridharangoji wrote on Wed, 04 February 2015 08:34
First of all in your query 'values' keyword is missed. So ERROR at line 1:
ORA-00928: missing SELECT keyword occurred.


That's just repeating what Blackswan already pointed out.

sridharangoji wrote on Wed, 04 February 2015 08:34

Second : if you want datatype conversation use To_date function with formatting as following.

Insert into a values(To_date('2011-07-20 20:57:48', 'YYYY-MM-DD HH24:MI:SS'));

That'll error out with the OPs actual data.

Report message to a moderator

Previous Topic: I want find how many hour,minutes,seconds is taken from task_stared and task_end.
Next Topic: What is the effect of index for DELETE operation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Oct 20 17:19:00 CDT 2026
.:: Forum Home :: Wiki Home :: Contact :: Privacy ::.