• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Smart Notification

SQL Date Syntax

 5 Replies
 0 Subscribed to this topic
 14 Subscribed to this forum
Sort:
Author
Messages
Ken Ptak
Basic Member Send Private Message
Posts: 20
Basic Member
11/14/2007 3:07 PM
Hello - I have a query that creates an Infoset using a JDBC connection successfully. I want to limit the records being returned by the Infoset where the PER_END_DATE is after July 1, 2007.

I receive the error “Error: ORA-01843: not a valid month”
When I try to add the following criteria to the SQL statement:
HAVING ((GMDISTRIB.PER_END_DATE)>'07/01/2007')

I’ve tried to play with the formatting of the date but I haven’t found the correct format…
Any suggestions/ideas?
Thanks,
Ken
Will
Veteran Member Send Private Message
Posts: 39
Veteran Member
11/14/2007 3:23 PM
Hi Ken,

You need to cast those strings you are passing in the HAVING statement to something SQL can understand and use comparison (>) with.

HAVING (CAST(GMDISTRIB.PER_END_DATE AS datetime)>CAST('07/01/2007' AS datetime))
should work. Now you are comparing a date object with a date object.

Guillaume
Ken Ptak
Basic Member Send Private Message
Posts: 20
Basic Member
11/14/2007 4:12 PM
That makes sense, but it didn't quite work...

When I enered:
HAVING (CAST(GMDISTRIB.PER_END_DATE AS datetime)>CAST('07/01/2007' AS datetime))

I received the error:
Error: ORA-00902: invalid datatype

So I modified the query to read:
HAVING (CAST(GMDISTRIB.PER_END_DATE AS date)>CAST('07/01/2007' AS date))
Which removes the 'Invalid datatype' error message but I still get the original message:
Error: ORA-01843: not a valid month

Any other ideas? Thanks in advance!
Will
Veteran Member Send Private Message
Posts: 39
Veteran Member
11/14/2007 5:17 PM
Oh, we are talking Oracle here, sorry I did not catch it.

What format is GMDISTRIB.PER_END_DATE in your table?
John Henley
Send Private Message
Posts: 3351
11/14/2007 5:39 PM
Ken, since this is Oracle, you probably need to make sure you're entering the dates in the (default) format defined in the database, which is usually dd-MMM-ccyy (I think that is it--off the top of my head). Generally with Oracle, I avoid that altogether by using the to_date() function:
HAVING (GMDISTRIB.PER_END_DATE>to_date('2007-07-01','yyyy-mm-dd'))
Thanks for using the LawsonGuru.com forums!
John
Ken Ptak
Basic Member Send Private Message
Posts: 20
Basic Member
11/14/2007 6:24 PM
The to_date() function worked beautifully. Thanks for your help Guillaume and John!