TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Riddhi Valecha
443
3.3k
410.5k
Oracle Query - Invalid Date Error - PLease Help
Feb 17 2015 7:43 AM
Hi...
I want to develop a dynamic search query in toad (oracle 9i).
My Query is -
PROCEDURE MY_QUERY
(
IN_OPTION NUMBER,
IN_FROM DATE,
IN_TO DATE,
IN_COL1 NUMBER,
IN_COL2NUMBER,
IN_COL3 NUMBER,
OUT_CUR OUT RPT_CUR
)
AS
MY_QUERY VARCHAR2(4000);
MY_WHERE_CLAUSE VARCHAR2(4000) ;
IF IN_OPTION IS NOT NULL AND IN_OPTION= 1 THEN
MY_WHERE_CLAUSE := MY_WHERE_CLAUSE || 'AND DATE_COL1 BETWEEN' || TO_DATE(IN_FROM,'dd-MM-yyyy') || 'AND' || TO_DATE(IN_DATE_TO,'dd-MM-yyyy');
END IF;
IF IN_OPTION IS NOT NULL AND IN_OPTION= 2 THEN
MY_WHERE_CLAUSE := MY_WHERE_CLAUSE || 'AND DATE_COL2 BETWEEN' || TO_DATE(IN_FROM,'dd-MM-yyyy') || 'AND' || TO_DATE(IN_DATE_TO,'dd-MM-yyyy');
END IF;
IF IN_COL1 IS NOT NULL THEN
MY_WHERE_CLAUSE := MY_WHERE_CLAUSE || 'AND COL5 = ' || IN_COL1;
END IF;
MY_QUERY := MY_QUERY || ' SELECT vwo.COL3, vwo.COL4, ';
MY_QUERY := MY_QUERY || ' (SELECT cm.COL2 from TABLE_MASTER_1 cm where cm.DELETED_STATUS = 0 and cm.TID = vwo.COMPANY_ID )as Company ';
MY_QUERY := MY_QUERY || ' from TABLE_12 vwo where
vwo.COL_17 IN ';
MY_QUERY := MY_QUERY || ' ( ';
MY_QUERY := MY_QUERY || ' SELECT vcomp.COL_17 from TABL_17 vcomp where vcomp.DELETED_STATUS = 0 ';
MY_QUERY := MY_QUERY || MY_WHERE_CLAUSE ;
MY_QUERY := MY_QUERY || ' ) ';
How do I do this ??
Values in Date Variables -
Date_From (DateTime) - 01-01-2015
Date_To (DateTime) - 28-02-2015
ERROR - INVALID MONTH AT LINE 12.
PL/SQL ERROR.
-------
I also tried doing it withe VARCHAR Datatype and using TO_CHAR(Date_From,'dd-MM-yyyy') function. But this also did not work.
PLease help
Reply
Answers (
2
)
Unique key
Get Dynamic Data From DataTable