• Post Reply Bookmark Topic Watch Topic
  • New Topic

Need help with SQL Dates using Oracle  RSS feed

 
George Larry
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know I've done this before, but I can't remember how I had it. Could someone please help me out?
I'm wanting to SELECT an active assignment (one that has begun, but hasn't ended) so I've written this:
First... I get the date...
String bDate = request.getParameter( "bDate" );
ParsePosition pos1 = new ParsePosition(0);
java.util.Date uDate = new java.util.Date();
java.sql.Date bSqlDate = null;
And I check the date:
try {
uDate = ( java.util.Date )fDate.parse( bDate, pos1 );
bSqlDate = new java.sql.Date(uDate.getTime());
} catch ( Exception e ) {
out.println( "Error " + e );
}
Then I run my query:
query = "SELECT ASSIGNREFNUM FROM ASSIGNMENTS WHERE SERVSTART < '" + bSqlDate + "' AND SERVEND > '" + bSqlDate + "' AND SERVICEID = " + sNum;
I thought this was the correct way to do this... Any ideas on what I'm doing wrong? I get the ora-01861 literal does not match string error when I try to execute the query.
Here's my query... displayed:
SELECT ASSIGNREFNUM FROM ASSIGNMENTS WHERE SERVSTART < '2002-06-07' AND SERVEND > '2002-06-07' AND SERVICEID = 78614
[ June 14, 2002: Message edited by: George Larry ]
 
Nicole Swan
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I may be wrong, but doesn't Oracle require a date in a format similar to: 07-JUN-2002
 
Jason Menard
Sheriff
Posts: 6450
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not being a DBA, I don't know the some of the details, but in our Oracle dates are stored as in '14-JUN-02', not '2002-06-14'. A select with the second format gives the error you describe, but using a select with the first format works cleanly for me. Maybe do a SELECT * and see how the dates are stored in your table? In my case, a TIMESTAMP is actually what's stored, so asking for dates > '13-JUN-02' will return all dates from the 13th as well as from the 14th.
 
George Larry
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there an easy way to change the format of my date?
I have it as a String looking like this: 06/07/02 (June 07, 2002). I have that same string converted to a java.sql.Date, but I need it to be in the format Oracle can use '06-JUN-02' Does anyone know how to make that happen?
Thanks.
[ June 17, 2002: Message edited by: George Larry ]
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!