error on inserting a Date field in Oracle 8i

[wwworg]

Hey guys

I am having trouble inserting a record into oracle using JSP. The error is related to the date fields. The exact error is

"javax.servlet.ServletException: ORA-01843: not a valid month".

Now here is how I am making the date

Date today1 = new Date();
int thisDay = today1.getDate();
int thisMonth = today1.getMonth()+1;
int thisYear = today1.getYear()+1900;
String goodDate = thisMonth+"/"+thisDay+"/"+thisYear;

and here is how I am inserting it

String insertQuery = "INSERT INTO JOB_REQUISITION("
+ "JBR_DEPT_TBLGRPCD, "
+ "JBR_UNITNO, "
+ "JBR_SEQ_NBR, "
+ "JBR_REQUEST_ID, "
+ "JBR_REQUEST_DT, "
+ "JBR_STS_CD, "
+ "JBR_STS_DT, "
+ "JBR_JOB_ORG_ID, "
+ "JBR_JOB_TBLGRPCD, "
+ "JBR_JOB_CLASS, "
+ "JBR_LST_UPDT_IP_ADDR, "
+ "JBR_LST_UPDT_ID, "
+ "JBR_LST_UPDT_TS) VALUES('"
+ dptGrpCode + "', '"
+ department + "', "
+ nextJobReqNum + ", '"
+ ofcLogin.getEscUser() + "', '"
+ goodDate + "', 'OP', '"
+ goodDate + "', '"
+ organization + "', '"
+ jobGrpCode + "', '', '"
+ remoteIp + "', '"
+ ofcLogin.getEscUser() + "', '"
+ goodDate + "')";

As you can see, I am trying to insert a date at 3 points. I dont understand why Oracle doesnt like the month??? Any thoughts??

Thanks

 

[LKBrwnDBA]

Because of the default date format (NLS_DATE_FORMAT)

You have to change this:

String goodDate = thisMonth+"/"+thisDay+"/"+thisYear;

To this:

String goodDate = "TO_CHAR('"+thisMonth+thisDay+thisYear+",'MMDDYYYY')";


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[wwworg]

Thanks for your reply, well the "not a valid month" exception is gone but now i get another error

"javax.servlet.ServletException: ORA-00917: missing comma".

Here is where I am making the string as you suggested

Date today1 = new Date();
int thisDay = today1.getDate();
int thisMonth = today1.getMonth()+1;
int thisYear = today1.getYear()+1900;
String goodDate = "TO_CHAR('"+thisMonth+thisDay+thisYear+",'MMDDYYYY')";

and here is the sql

String insertQuery = "INSERT INTO JOB_REQUISITION("
+ "JBR_DEPT_TBLGRPCD, "
+ "JBR_UNITNO, "
+ "JBR_SEQ_NBR, "
+ "JBR_REQUEST_ID, "
+ "JBR_REQUEST_DT, "
+ "JBR_STS_CD, "
+ "JBR_STS_DT, "
+ "JBR_JOB_ORG_ID, "
+ "JBR_JOB_TBLGRPCD, "
+ "JBR_JOB_CLASS, "
+ "JBR_LST_UPDT_IP_ADDR, "
+ "JBR_LST_UPDT_ID, "
+ "JBR_LST_UPDT_TS) VALUES('"
+ dptGrpCode + "', '"
+ department + "', "
+ nextJobReqNum + ", '"
+ ofcLogin.getEscUser() + "', '"
+ goodDate + "', 'OP', '"
+ goodDate + "', '"
+ organization + "', '"
+ jobGrpCode + "', '', '"
+ remoteIp + "', '"
+ ofcLogin.getEscUser() + "', '"
+ goodDate + "')";

when it tries to execute the sql statement, thats when it gives the "missing comma" error. I checked the string, all the comma's are there.

Thanks

 

[LKBrwnDBA]

Missing quote maybe?:

String goodDate = "TO_CHAR('"+thisMonth+thisDay+thisYear+"','MMDDYYYY')";
+----------------------------------------------------------------------^ here


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[wwworg]

I added that quote right after I posted my previous thread
but it is still giving me a comma exception. Cant seem to find where and why

Thanks

 

[ddiamond]

Remove the single quotes around good date

String insertQuery = "INSERT INTO JOB_REQUISITION("
+ "JBR_DEPT_TBLGRPCD, "
+ "JBR_UNITNO, "
+ "JBR_SEQ_NBR, "
+ "JBR_REQUEST_ID, "
+ "JBR_REQUEST_DT, "
+ "JBR_STS_CD, "
+ "JBR_STS_DT, "
+ "JBR_JOB_ORG_ID, "
+ "JBR_JOB_TBLGRPCD, "
+ "JBR_JOB_CLASS, "
+ "JBR_LST_UPDT_IP_ADDR, "
+ "JBR_LST_UPDT_ID, "
+ "JBR_LST_UPDT_TS) VALUES('"
+ dptGrpCode + "', '"
+ department + "', "
+ nextJobReqNum + ", '"
+ ofcLogin.getEscUser() + "', '"
+ goodDate + "', 'OP', '"
+ goodDate + "', '"
+ organization + "', '"
+ jobGrpCode + "', '', '"
+ remoteIp + "', '"
[blue]+ ofcLogin.getEscUser() + "', "
+ goodDate + ")";[/blue]