Home » SQL & PL/SQL » SQL & PL/SQL » Inserting value from varchar2 to date field (oracle 10g)
Inserting value from varchar2 to date field [message #313278] Fri, 11 April 2008 05:24 Go to next message
someswar1
Messages: 53
Registered: January 2008
Member
hi,
I am creating a table as create table tab_dt(activation_dt varchar2(20));
and insert some value like insert into tab_dt values('15/5/2008 2:02:00 A.m');

And created another table create table tab_dt_1(activation_dt date);

how can i insert the data in tab_dt_1 table in yyyy-mm-dd hh24:mi:ss format

Regards:
someswar

Re: Inserting value from varchar2 to date field [message #313280 is a reply to message #313278] Fri, 11 April 2008 05:27 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
study TO_DATE functions and it's format specifier's.

In Date field you can store date.And can retrieve it in any format using TO_CHAR function.
Re: Inserting value from varchar2 to date field [message #313281 is a reply to message #313278] Fri, 11 April 2008 05:29 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Do NOT store dates as strings. EVER.
Re: Inserting value from varchar2 to date field [message #313343 is a reply to message #313278] Fri, 11 April 2008 07:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
someswar1 wrote on Fri, 11 April 2008 06:24

how can i insert the data in tab_dt_1 table in yyyy-mm-dd hh24:mi:ss format



There is no such thing as a format for a DATE column in a database. You only output a DATE in a format.
Re: Inserting value from varchar2 to date field [message #313390 is a reply to message #313278] Fri, 11 April 2008 10:48 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Okay, I have a soft spot for people with date problems. The first thing you have to remember is that '15/5/2008 2:02:00 A.m' is NOT A DATE!!! It is a character string. Our brains make it a "date" because that's what we're used to.

Oracle has a DATE type, which you used in your second table (tab_dt_1). Oracle stores information in a DATE type field as a decimal number. It is up to YOU to take the string you have and convert it to an Oracle DATE type. This is done with the TO_DATE function. Basically the function takes your string and generates the decimal number that Oracle stores in you DATE type field.

insert into tab_dt_1 (activation_date) 
       values (to_date('15/5/2008 2:02:00 A.m','DD/MM/RRRR HH:MI:SS'));


Once it's in your table as a DATE type, you can retrieve and format the value in any way you want. Look for the TO_CHAR function to see how to create the string you want from your DATE type.


HTH,
Ron
Previous Topic: select only structure of table ,no any rows
Next Topic: ORA-02055 ORA-00001and :new
Goto Forum:
  


Current Time: Sat Dec 03 14:21:35 CST 2016

Total time taken to generate the page: 0.11273 seconds