Home » SQL & PL/SQL » SQL & PL/SQL » date conversion
date conversion [message #253580] Tue, 24 July 2007 06:15 Go to next message
annu-agi
Messages: 202
Registered: July 2005
Location: Karachi
Senior Member

hi experts

i have column
mem_dt varchar2(Cool
and the data is like

20061211
20061206
20061207
20061207
20061209
19980709
20061211
20061212
20061212

now i would like to convert all the data and wana to keep in one date feild
join_date date


for that i tried this

1 update dealer_info
2 set join_date=
3* to_Date(substr(mem_dt,7,2)||'-'||substr(mem_dt,5,2)|| '-'||substr(mem_Dt,1,4),'dd-mon-yyyy')
SQL> /
to_Date(substr(mem_dt,7,2)||'-'||substr(mem_dt,5,2)|| '-'||substr(mem_Dt,1,4),'dd-mon-yyyy')
*
ERROR at line 3:
ORA-01843: not a valid month


will u pleas help me what i m doing wrong and what is the solution
Re: date conversion [message #253589 is a reply to message #253580] Tue, 24 July 2007 06:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
no need for all the substrings

SQL> select to_date('20061211','yyyymmdd') from dual;

TO_DATE('
---------
11-DEC-06
Re: date conversion [message #253596 is a reply to message #253589] Tue, 24 July 2007 06:31 Go to previous messageGo to next message
annu-agi
Messages: 202
Registered: July 2005
Location: Karachi
Senior Member

thank you guru
Re: date conversion [message #253599 is a reply to message #253596] Tue, 24 July 2007 06:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Thankyou for the accolade, but it is unwarranted. I'm no guru. Smile
Re: date conversion [message #253667 is a reply to message #253589] Tue, 24 July 2007 10:05 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Your procedure failed because you did not have the correct date format for the string you created with your substring commands


i have column 
mem_dt varchar2(
and the data is like 

20061211
20061206
20061207
20061207
20061209
19980709
20061211
20061212
20061212

1 update dealer_info
2 set join_date=
3* to_Date(substr(mem_dt,7,2)||'-'||substr(mem_dt,5,2)|| '-'||substr(mem_Dt,1,4),'dd-mon-yyyy')
SQL> /
to_Date(substr(mem_dt,7,2)||'-'||substr(mem_dt,5,2)|| '-'||substr(mem_Dt,1,4),'dd-mon-yyyy')


you're creating the string '11-12-2006', and then comparing it to the date format 'dd-mon-yyyy', which is incorrect. If you had used 'dd-mm-yyyy', your statement would have functioned properly.

Of course, the recommendation of using...

...
set join_date = to_date('20061211','yyyymmdd');


is much cleaner and easier to understand.


Once you have the string converted to a DATE TYPE, you can then use the to_char function to place the date in the format you want.

So, try the following...

select to_date('20061211','yyyymmdd') from dual;

select to_char(to_date('20061211','yyyymmdd'),'mm/dd/yyyy') from dual;
select to_char(to_date('20061211','yyyymmdd'),'dd-mon-yyyy') from dual;
select to_char(to_date('20061211','yyyymmdd'),'DD-Mon-YYYY') from dual;
select to_char(to_date('20061211','yyyymmdd'),'fmDay, Month ddth, YYYY') from dual;


The most difficult concept with dates is remembering that 12-DEC-06 is a STRING. to_date('12-DEC-06','DD-MON-RR') is a date.

HTH,
Ron
Previous Topic: query
Next Topic: error in trigger
Goto Forum:
  


Current Time: Tue Dec 06 08:27:10 CST 2016

Total time taken to generate the page: 0.06146 seconds