Home » SQL & PL/SQL » SQL & PL/SQL » URGENT PLEASE HELP! (Oracle 8i)
icon2.gif  URGENT PLEASE HELP! [message #359622] Mon, 17 November 2008 12:58 Go to next message
sbat0991
Messages: 3
Registered: November 2008
Junior Member
Okay,
so here's the deal. I have a database with over 1,000 records in which users upload records on the first of each month. However we've recently found that for the past three months (Aug, Sept, and Oct) contain wrong information. I am basically trying to delete only records from the past three months.I've decided to delete by the date column. Here is the statement that I thought would work:

Delete from Weapon_data
Where Disposition_data >= '08/01/2008';

When i tested this statement it deleted every record in my trial database. Is there something that I an doing wrong?
Re: URGENT PLEASE HELP! [message #359624 is a reply to message #359622] Mon, 17 November 2008 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Okay, here's the deal, read OraFAQ Forum Guide and
- don't post your title in UPPER case
- don't use urgent
- post a meaningful title

What's wrong? '08/01/2008' is a string not a date.

Regards
Michel
Re: URGENT PLEASE HELP! [message #359625 is a reply to message #359622] Mon, 17 November 2008 13:13 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, you are comparing a DATE column to a character string. Please define URGENT. It seems it is urgent for you, not for us.
Re: URGENT PLEASE HELP! [message #359627 is a reply to message #359624] Mon, 17 November 2008 13:21 Go to previous messageGo to next message
sbat0991
Messages: 3
Registered: November 2008
Junior Member
If this is a string, than how should it be stated? I figured the way that I was writing it was the problem.
I am not sure how to write the statement correctly.
Re: URGENT PLEASE HELP! [message #359631 is a reply to message #359627] Mon, 17 November 2008 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe using TO_DATE function.

Regards
Michel
Re: URGENT PLEASE HELP! [message #359635 is a reply to message #359622] Mon, 17 November 2008 14:31 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
What is the column Disposition_data defined as?
Re: URGENT PLEASE HELP! [message #359639 is a reply to message #359635] Mon, 17 November 2008 15:32 Go to previous messageGo to next message
sbat0991
Messages: 3
Registered: November 2008
Junior Member
the column is defined as VARCHAR2(20). I believe since it was not defined using the TO_DATE function, i do not think that will work
Re: URGENT PLEASE HELP! [message #359643 is a reply to message #359622] Mon, 17 November 2008 16:09 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Thats why it did'nt work. NEVER store a date in a varchar2 column, always use date or timestamp. what you did was delete every row where the string was >= the string '08/01/2008'. if you have to, rewrite it as the following. For purpose of the example, I will assume that Disposition_data has a string of mm/dd/yyyy

Delete from Weapon_data
Where to_date(Disposition_data,'MM/DD/YYYY') >= TO_DATE('08/01/2008','MM/DD/YYYY');


What happened in your query the string '08/01/2008' would be less then the string '09/10/2001'
Re: URGENT PLEASE HELP! [message #360002 is a reply to message #359622] Wed, 19 November 2008 03:27 Go to previous messageGo to next message
navneet_iway
Messages: 2
Registered: November 2008
Junior Member
hi ! if wan to delete the row which is creted in the month of (aug,sep,oct)then use the following syntax of sql queries

delete from Weapon_data where Disposition_data between to_date('01-aug-81','dd-mon-rr') and to_date('31-oct-81','dd-mon-rr');
Re: URGENT PLEASE HELP! [message #360006 is a reply to message #360002] Wed, 19 November 2008 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Use date with 4 figures, you will be sure to point to the correct century
2/ This will not delete rows if the last date after 00:00:00

Regards
Michel
Re: URGENT PLEASE HELP! [message #361608 is a reply to message #359622] Thu, 27 November 2008 03:17 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

Hello, you can also delete by converting the date into character data type..use the TO_CHAR function

Eg..

DELETE table_name
WHERE TO_CHAR(DATE) = 'DATE'
Re: URGENT PLEASE HELP! [message #361620 is a reply to message #361608] Thu, 27 November 2008 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Very bad idea if you want:
- to use indexes
- to work on date range like here

Regards
Michel
Re: URGENT PLEASE HELP! [message #361747 is a reply to message #359622] Thu, 27 November 2008 20:46 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

how about this..

DELETE table_name
WHERE DATE_COLUMN >= TO_DATE('DD-MON-RRRR');

Re: URGENT PLEASE HELP! [message #361752 is a reply to message #361747] Thu, 27 November 2008 22:10 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ice_cream_hehe wrote on Fri, 28 November 2008 08:16
how about this..

DELETE table_name
WHERE DATE_COLUMN >= TO_DATE('DD-MON-RRRR');




How can you compare Character with date ?

Smile
Rajuvan.

[Updated on: Thu, 27 November 2008 22:10]

Report message to a moderator

Re: URGENT PLEASE HELP! [message #361754 is a reply to message #359622] Thu, 27 November 2008 22:33 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

Rajuvan,

it can compare..
both are having DATE data types

TO_DATE function converts a date formatted string to date..

Smile



Re: URGENT PLEASE HELP! [message #361755 is a reply to message #359639] Thu, 27 November 2008 22:41 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ice_cream_hehe

sbat0991 wrote on Tue, 18 November 2008 03:02
the column is defined as VARCHAR2(20). I believe since it was not defined using the TO_DATE function, i do not think that will work


Please have the patience of reading through the replies. This has been already answered by @Bill B in this post.


Regards,
Jo

[Updated on: Thu, 27 November 2008 22:41]

Report message to a moderator

Re: URGENT PLEASE HELP! [message #361756 is a reply to message #359622] Thu, 27 November 2008 22:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

hehe ,

But it is ot recommended at all as conversion depends on the NLS settings.

SQL> SELECT * FROM TT;

CHAR_DATE
----------
10/10/2008
1/1/2008
10/1/2008
15/10/2008
25/10/2009

SQL> SELECT * FROM TT WHERE CHAR_DATE  > SYSDATE;
SELECT * FROM TT WHERE CHAR_DATE  > SYSDATE
                       *
ERROR at line 1:
ORA-01843: not a valid month


SQL>


Smile
Rajuvan.
Re: URGENT PLEASE HELP! [message #361757 is a reply to message #359622] Thu, 27 November 2008 22:59 Go to previous messageGo to next message
ice_cream_hehe
Messages: 11
Registered: November 2008
Location: Manila, Philippines
Junior Member

okay..
Re: URGENT PLEASE HELP! [message #362283 is a reply to message #361747] Mon, 01 December 2008 12:32 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
ice_cream_hehe wrote on Thu, 27 November 2008 21:46


TO_DATE('DD-MON-RRRR');




And what is this? TO_DATE takes on two parameters.
Previous Topic: inserting the data in the deleted part of a table
Next Topic: SQL query efficiency
Goto Forum:
  


Current Time: Fri Dec 02 20:29:41 CST 2016

Total time taken to generate the page: 0.20927 seconds