Home » SQL & PL/SQL » SQL & PL/SQL » TO_CHAR & TO_DATE
TO_CHAR & TO_DATE [message #195529] Fri, 29 September 2006 04:29 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
i HAVE TABLE AND DATA LIKE
eid	stmt
1	060911	
2	060912	
5	060914	
3	060925	
12	060931	
7	060921	


data type would be stmt is varchar.
and data is kept as yymmdd.
now i have one parameter which is coming as date format like dd-mon-yyyy.

now i have to filter the data based of parameter
then what would be casting done

and how to get rows only which has sysdate-3 or sysdate+3


[Updated on: Fri, 29 September 2006 05:00]

Report message to a moderator

Re: TO_CHAR & TO_DATE [message #195532 is a reply to message #195529] Fri, 29 September 2006 05:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're keeping a date in the column Stmt, then the only correct datatype for it is DATE.
Anything else is just making hard work for yourself, and increases the chances of errors later on.
Change it.

If you ABSOLUTELY have to store a date as a string, then store it as a format with a 4 chr year - Did we learn NOTHING from the whole Y2K problem?

The query you're looking for is (assuming I've understood the question correctly:
SELECT *
FROM   table
WHERE  to_date(stmt,'yymmdd') between (sysdate-3) and (sysdate+3)

Re: TO_CHAR & TO_DATE [message #195537 is a reply to message #195532] Fri, 29 September 2006 05:50 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
the column stmt is holding value to make a record of particular date.

Now is there any way to back convert into stmt into date format and compare with parameter which is actully in date format.

I have only options for parameter to change.

the query does end up with 0ra-01839: date not valid for month specified


here is script for data for testing
drop table xdemo
CREATE TABLE xdemo 
(EID NUMBER,
STMT VARCHAR2(20))

INSERT INTO xdemo VALUES(7,'060921');
INSERT INTO xdemo VALUES(1,'060922');
INSERT INTO xdemo VALUES(6,'060924');
INSERT INTO xdemo VALUES(4,'060927');


now i want to get records only which is for sysdate-3 in stmt column.

logically only one result ie id 4

i guess i am not confusing



Re: TO_CHAR & TO_DATE [message #195540 is a reply to message #195537] Fri, 29 September 2006 06:20 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:

the query does end up with 0ra-01839: date not valid for month specified


This is because you are not using a DATE column.
You have got data in there which is vannot be converted into a valid date using the YYMMDD date format.
If you were using a DATE field, you could not get into this position, as all the data would have been validated on entry.
This is what I meant when I said 'increases the chances of errors'

First you need to tidy up your data.
Create this function:
CREATE OR REPLACE FUNCTION valid_date (p_string IN  VARCHAR2
                                      ,p_format IN  VARCHAR2)
                                      RETURN VARCHAR2 IS
  v_date    DATE;
BEGIN
  v_date := TO_DATE(p_string,p_format);
  RETURN 'Y';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'N';
END valid_date;


And then execute this query:
UPDATE xdemo
SET    stmt = null
WHERE  valid_date(stmt,'yymmdd') = 'N';


This will set the column STMT to null in all the rows where it doesn't contain a valid date.

Now you can run this query:
SELECT *
FROM   xdemo
WHERE  to_date(stmt,'yymmdd') = trunc(sysdate)-3;


But really - change STMT to a DATE type.
You're storing a date, so use a DATE column. Anything else is bad practice and causes problems.
Previous Topic: Oracle and C integration...............
Next Topic: retrieval time
Goto Forum:
  


Current Time: Tue Dec 06 06:34:38 CST 2016

Total time taken to generate the page: 0.09210 seconds