Home » SQL & PL/SQL » SQL & PL/SQL » how to convert date to specific format? (11g)
icon5.gif  how to convert date to specific format? [message #568338] Thu, 11 October 2012 00:48 Go to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi all,

Hope doing well,

sir i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM'
and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.

could you help me.

thanks
Re: how to convert date to specific format? [message #568340 is a reply to message #568338] Thu, 11 October 2012 01:05 Go to previous messageGo to next message
dude4084
Messages: 213
Registered: March 2005
Location: Mux
Senior Member
THe date displayed seems to be compatible with date format. Try using "to_date" format while inserting.
Re: how to convert date to specific format? [message #568346 is a reply to message #568340] Thu, 11 October 2012 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not "try" ALWAYS use TO_DATE WITH a format string to convert a string to a date and if you use month name add the NLS_DATE_LANGUAGE parameter to TO_DATE call.

Regards
Michel
Re: how to convert date to specific format? [message #568352 is a reply to message #568346] Thu, 11 October 2012 02:22 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Simply you can try..

declare
v_date date;
begin
v_date:= sysdate;

dbms_output.put_line(v_date);
execute immediate 'alter session set nls_date_format = ''mm/dd/yyyy hh24:mi:ss''';
dbms_output.put_line(v_date);
insert into test(modified_on) values (v_date);
commit;
end;
/
Re: how to convert date to specific format? [message #568354 is a reply to message #568352] Thu, 11 October 2012 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As v_date is of DATE datatype your ALTER SESSION is completly useless.

I see what you mean but useless to explain it it is a bad solution. The correct one is to use TO_DATE.

Regards
Michel
Re: how to convert date to specific format? [message #568355 is a reply to message #568354] Thu, 11 October 2012 02:28 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Instead of convert TO_DATE,you can use... what is the problem now>?
Re: how to convert date to specific format? [message #568360 is a reply to message #568355] Thu, 11 October 2012 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bad coding is the problem, don't you see it?

Regards
Michel
Re: how to convert date to specific format? [message #568405 is a reply to message #568346] Thu, 11 October 2012 06:13 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir i am inserting this value in my table which is:

INSERT INTO tt_v_templeave(empid, ldates) VALUES('00000001', To_Date('10-OCT-12 11.30.54 AM','DD-MM-YY'));

but the problem is this table has Tid column which is number datatype Creating identity number.
i already created sequence and trigger for that.
but still it's not inserting data in this the error is like this.

SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.

thanks
Re: how to convert date to specific format? [message #568406 is a reply to message #568405] Thu, 11 October 2012 06:21 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your date doesn't match your format mask
Re: how to convert date to specific format? [message #568408 is a reply to message #568406] Thu, 11 October 2012 06:23 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Hi sir i have to get data in this format 'DD-MM-YY'

how to get in this format data?
Re: how to convert date to specific format? [message #568409 is a reply to message #568408] Thu, 11 October 2012 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't know where you're getting the data from but the value in your insert isn't in that format.
If you don't want to insert the time component don't include it in the insert.
You do realize that dates aren't stored in any human readable format?
Re: how to convert date to specific format? [message #568410 is a reply to message #568409] Thu, 11 October 2012 06:28 Go to previous messageGo to next message
vikashkrburnwal@gmail.com
Messages: 61
Registered: October 2012
Location: Bangalore
Member
Then how to insert only date in that. i don't want to insert time there.
and why this identity column is not working.
Re: how to convert date to specific format? [message #568411 is a reply to message #568410] Thu, 11 October 2012 06:30 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

You can use
TRUNC (TO_DATE ('10-OCT-12 11.30.54 AM', 'DD-MON-YY HH:MI:SS AM'))


add TRUNC to to_date to avoid time component.

Regards,
Ram

[Updated on: Thu, 11 October 2012 06:31]

Report message to a moderator

Re: how to convert date to specific format? [message #568412 is a reply to message #568410] Thu, 11 October 2012 06:32 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
vikashkrburnwal@gmail.com wrote on Thu, 11 October 2012 12:28
Then how to insert only date in that. i don't want to insert time there.
and why this identity column is not working.

It's erroring out on the date, until you fix that you can't know if the identity column is working.
Re: how to convert date to specific format? [message #568413 is a reply to message #568410] Thu, 11 October 2012 06:34 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Vikas,

INSERT INTO tt_v_templeave(empid, ldates) VALUES('00000001', To_Date('10-OCT-12 11.30.54 AM','DD-MM-YY'));


Quote:

SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected


> 10-10-12: "-"(hyphen) is not a number, Instead You could try 101012 (provided you should remember what is 101012).

> Storing Date in a number column is a bad practice.

Regards,
Veeresh
Re: how to convert date to specific format? [message #568418 is a reply to message #568413] Thu, 11 October 2012 06:48 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
bhat.veeresh@gmail.com wrote on Thu, 11 October 2012 12:34

> 10-10-12: "-"(hyphen) is not a number, Instead You could try 101012 (provided you should remember what is 101012).

> Storing Date in a number column is a bad practice.


Re-read the first post. OP is inserting the date into a date column.
The error is because the date doesn't match the format mask as I already pointed out.
Re: how to convert date to specific format? [message #568419 is a reply to message #568413] Thu, 11 October 2012 06:48 Go to previous message
Michel Cadot
Messages: 58628
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v.ram81 gave you the solution.

Regards
Michel
Previous Topic: Function very slow after Oracle upgrade
Next Topic: Query to convert data in single column
Goto Forum:
  


Current Time: Thu Jul 31 01:03:18 CDT 2014

Total time taken to generate the page: 0.12023 seconds