Oneday, a problem was reported to me:
The user has tried the sql: SELECT COUNT(*) AS CNT FROM EMP WHERE DOB >='1-APR-1990'
He got the output as 120.
But the Problem is, whatever date he is giving,always the output was 120 only.
It was found out that the data type was VARCHAR2 instead of DATE.
Now letus see the step by step procedure to change the data type:
sql> ALTER TABLE EMP RENAME COLUMN DOB TO DOBXXXX;
sql> ALTER TABLE EMP ADD DOB DATE;
sql> UPDATE EMP SET DOB=TO_DATE(DOBXXXX,'DD/MM/YYYY');
check whether all the data tranferred successfully, then commit.
sql> ALTER TABLE EMP DROP COLUMN DOBXXXX;