OraFAQ Forum: SQL & PL/SQL » how to convert date to specific format?

Home » SQL & PL/SQL » SQL & PL/SQL » how to convert date to specific format? (11g)

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

Switch to threaded view of this topic

Create a new topic

Submit Reply

how to convert date to specific format? [message #568338]

Thu, 11 October 2012 00:48

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

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568340 is a reply to message #568338]

Thu, 11 October 2012 01:05

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.

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568346 is a reply to message #568340]

Thu, 11 October 2012 01:33

Michel Cadot
Messages: 54226
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

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568352 is a reply to message #568346]

Thu, 11 October 2012 02:22

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;
/

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568354 is a reply to message #568352]

Thu, 11 October 2012 02:26

Michel Cadot
Messages: 54226
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

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568355 is a reply to message #568354]

Thu, 11 October 2012 02:28

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

Instead of convert TO_DATE,you can use... what is the problem now>?

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568360 is a reply to message #568355]

Thu, 11 October 2012 02:48

Michel Cadot
Messages: 54226
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Bad coding is the problem, don't you see it?

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568405 is a reply to message #568346]

Thu, 11 October 2012 06:13

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

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568406 is a reply to message #568405]

Thu, 11 October 2012 06:21

cookiemonster
Messages: 9166
Registered: September 2008
Location: Rainy Manchester

Senior Member

Your date doesn't match your format mask

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568408 is a reply to message #568406]

Thu, 11 October 2012 06:23

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?

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568409 is a reply to message #568408]

Thu, 11 October 2012 06:24

cookiemonster
Messages: 9166
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?

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568410 is a reply to message #568409]

Thu, 11 October 2012 06:28

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.

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568411 is a reply to message #568410]

Thu, 11 October 2012 06:30

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

Send a private message to this user

Re: how to convert date to specific format? [message #568412 is a reply to message #568410]

Thu, 11 October 2012 06:32

cookiemonster
Messages: 9166
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.

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568413 is a reply to message #568410]

Thu, 11 October 2012 06:34

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

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568418 is a reply to message #568413]

Thu, 11 October 2012 06:48

cookiemonster
Messages: 9166
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.

Report message to a moderator

Send a private message to this user

Re: how to convert date to specific format? [message #568419 is a reply to message #568413]

Thu, 11 October 2012 06:48

Michel Cadot
Messages: 54226
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

v.ram81 gave you the solution.

Regards
Michel

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 05:55:10 CDT 2013

Total time taken to generate the page: 0.09244 seconds