By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

its a basic SQL command but require help ?

P: n/a
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan
Jul 19 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan

Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
....

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you
looked in the manual? There are other ways of doing it, but you have not
given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is
(it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim
Jul 19 '05 #2

P: n/a
"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<WNCCb.38630$8y1.163387@attbi_s52>...
"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan

Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
...

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you
looked in the manual? There are other ways of doing it, but you have not
given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is
(it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim


Hello Jim,

Thanx for ur suggestion. I couldnt understand wat extra details u need
to suggest a single step to update the month. i tell u the full
details now.

I have a table with empno,ename,sal,hire_date etc.In the hire_date
column i made mistake in month ie..instead of giving as 12-Nov-03, I
have given as 12-Sep-03. I have 20 rows with the same type of
mistake.I want to have a single or a couple of SQL statement which
could solve this error rather than updating each 20 rows.

Hope u have got my problem. Can u help me???

In my question of crystal report. I want to know What is it? Wats its
use? Wats its advantages? where it can be applied (i mean what type of
objects and situations) ??

regards,
Cherrish Vaidiyan
Jul 19 '05 #3

P: n/a
"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<WNCCb.38630$8y1.163387@attbi_s52>...
"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan

Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
...

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you
looked in the manual? There are other ways of doing it, but you have not
given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is
(it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim


Hello Jim,

Thanx for ur suggestion. I couldnt understand wat extra details u need
to suggest a single step to update the month. i tell u the full
details now.

I have a table with empno,ename,sal,hire_date etc.In the hire_date
column i made mistake in month ie..instead of giving as 12-Nov-03, I
have given as 12-Sep-03. I have 20 rows with the same type of
mistake.I want to have a single or a couple of SQL statement which
could solve this error rather than updating each 20 rows.

Hope u have got my problem. Can u help me???

In my question of crystal report. I want to know What is it? Wats its
use? Wats its advantages? where it can be applied (i mean what type of
objects and situations) ??

regards,
Cherrish Vaidiyan
Jul 19 '05 #4

P: n/a

"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
"Jim Kennedy" <ke****************************@attbi.net> wrote in message

news:<WNCCb.38630$8y1.163387@attbi_s52>...
"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan

Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
...

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you looked in the manual? There are other ways of doing it, but you have not given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is (it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim


Hello Jim,

Thanx for ur suggestion. I couldnt understand wat extra details u need
to suggest a single step to update the month. i tell u the full
details now.

I have a table with empno,ename,sal,hire_date etc.In the hire_date
column i made mistake in month ie..instead of giving as 12-Nov-03, I
have given as 12-Sep-03. I have 20 rows with the same type of
mistake.I want to have a single or a couple of SQL statement which
could solve this error rather than updating each 20 rows.

Hope u have got my problem. Can u help me???

In my question of crystal report. I want to know What is it? Wats its
use? Wats its advantages? where it can be applied (i mean what type of
objects and situations) ??

regards,
Cherrish Vaidiyan


Are there only 20 rows in the whole table? If the whole table then:
update theTable set
hire_date=to_date(to_char(hire_date,'dd')||'/'||(to_char(hire_date,'mm')-1)|
|'/'||to_char(hire_date,'YYYY'),'dd/mm/yyyy');
got to seagate's web site and look up. Crystal reports is a report writer.
If you don't know what a report writer is then you don't need one.
Jim
Jul 19 '05 #5

P: n/a

"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
"Jim Kennedy" <ke****************************@attbi.net> wrote in message

news:<WNCCb.38630$8y1.163387@attbi_s52>...
"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google.c om...
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan

Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
...

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you looked in the manual? There are other ways of doing it, but you have not given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is (it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim


Hello Jim,

Thanx for ur suggestion. I couldnt understand wat extra details u need
to suggest a single step to update the month. i tell u the full
details now.

I have a table with empno,ename,sal,hire_date etc.In the hire_date
column i made mistake in month ie..instead of giving as 12-Nov-03, I
have given as 12-Sep-03. I have 20 rows with the same type of
mistake.I want to have a single or a couple of SQL statement which
could solve this error rather than updating each 20 rows.

Hope u have got my problem. Can u help me???

In my question of crystal report. I want to know What is it? Wats its
use? Wats its advantages? where it can be applied (i mean what type of
objects and situations) ??

regards,
Cherrish Vaidiyan


Are there only 20 rows in the whole table? If the whole table then:
update theTable set
hire_date=to_date(to_char(hire_date,'dd')||'/'||(to_char(hire_date,'mm')-1)|
|'/'||to_char(hire_date,'YYYY'),'dd/mm/yyyy');
got to seagate's web site and look up. Crystal reports is a report writer.
If you don't know what a report writer is then you don't need one.
Jim
Jul 19 '05 #6

P: n/a
Cherrish Vaidiyan wrote:
"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<WNCCb.38630$8y1.163387@attbi_s52>...
"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google .com...
hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan


Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
...

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you
looked in the manual? There are other ways of doing it, but you have not
given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is
(it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim

Hello Jim,

Thanx for ur suggestion. I couldnt understand wat extra details u need
to suggest a single step to update the month. i tell u the full
details now.

I have a table with empno,ename,sal,hire_date etc.In the hire_date
column i made mistake in month ie..instead of giving as 12-Nov-03, I
have given as 12-Sep-03. I have 20 rows with the same type of
mistake.I want to have a single or a couple of SQL statement which
could solve this error rather than updating each 20 rows.

Hope u have got my problem. Can u help me???

In my question of crystal report. I want to know What is it? Wats its
use? Wats its advantages? where it can be applied (i mean what type of
objects and situations) ??

regards,
Cherrish Vaidiyan


Hi Cherrish!

The statement you are looking for is:
UPDATE emp
SET hiredate = hiredate + (to_date('1-SEP-03')-to_date('1-NOV-03'));

I didnt see your previous post about Crystal Report so here's my reply
from what I could pick up:
Crystal Reports is used to display data from the database. You do not
have to know anything about SQL in order to use it - you just drag,
click and do stuff with your mouse. When you open the file, you can
either export it or print it. It is commonly used in conjunction with
Visual Basic or other programming languages used to develop front-ends
for database applications.

Best Regards,
Nitin

PS: Where are you from?

Jul 19 '05 #7

P: n/a
NicK <no****@emirates.net.ae> wrote in message news:<bs*********@news-dxb.emirates.net.ae>...
Cherrish Vaidiyan wrote:
"Jim Kennedy" <ke****************************@attbi.net> wrote in message news:<WNCCb.38630$8y1.163387@attbi_s52>...
"Cherrish Vaidiyan" <un***************@yahoo.com> wrote in message
news:f0**************************@posting.google .com...

hello googles,

I have a small sqlplus problem. i have created a table with date field
along with other varchar2,number etc. But unfortunately i made a
mistake in entering the date. for some date records i made an error in
month... say for 20 record.Now i need to correct the month.HOW????

i have tried with 'update' statement using like,=,... etc.All resulted
in failure.i thought of converting date datatype to character and then
use the 'update'.But its not working.What shall i do now ? should i
correct each and every record . Is this a drawback of Oracle????

regards,
Cherrish Vaidiyan

Keep it a date. use an update statement
update myTable set myfield=to_date('12/25/2003','mm/dd/yyyy') where
myfield=to_date('11/25/2003','mm/dd/yyyy');
...

for 20 yrows
commit;

This is assuming that you don't want the time componet of a date. Have you
looked in the manual? There are other ways of doing it, but you have not
given enough information to suggest them. That is there an easy way to
distinguish these rtecords from other records and thus write 1 update
statment instead of 20. You have another pst asking what Crystal Reports is
(it is a report writer), but the question is so basic that I shudder to
think what you could be doing connecting to a database.

Jim

Hello Jim,

Thanx for ur suggestion. I couldnt understand wat extra details u need
to suggest a single step to update the month. i tell u the full
details now.

I have a table with empno,ename,sal,hire_date etc.In the hire_date
column i made mistake in month ie..instead of giving as 12-Nov-03, I
have given as 12-Sep-03. I have 20 rows with the same type of
mistake.I want to have a single or a couple of SQL statement which
could solve this error rather than updating each 20 rows.

Hope u have got my problem. Can u help me???

In my question of crystal report. I want to know What is it? Wats its
use? Wats its advantages? where it can be applied (i mean what type of
objects and situations) ??

regards,
Cherrish Vaidiyan


Hi Cherrish!

The statement you are looking for is:
UPDATE emp
SET hiredate = hiredate + (to_date('1-SEP-03')-to_date('1-NOV-03'));

I didnt see your previous post about Crystal Report so here's my reply
from what I could pick up:
Crystal Reports is used to display data from the database. You do not
have to know anything about SQL in order to use it - you just drag,
click and do stuff with your mouse. When you open the file, you can
either export it or print it. It is commonly used in conjunction with
Visual Basic or other programming languages used to develop front-ends
for database applications.

Best Regards,
Nitin

PS: Where are you from?



************************************************** *************************

update Emp set Hiredate=Add_Months(Hiredate,2) where
HireDate='5-Sep-03' or put the condn which u wanted to...........I
hope this will resolve the issue

************************************************** ***************************
Jul 19 '05 #8

P: n/a
un***************@yahoo.com (Cherrish Vaidiyan) wrote in message news:<f0**************************@posting.google. com>...
hello googles,


Cherrish, not everyone uses google to access the newsgroups.
So unless you want to address only people who use google
to access this group, I would be careful with this greeting.
Jul 19 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.