Home » SQL & PL/SQL » SQL & PL/SQL » months_between
months_between [message #279211] Wed, 07 November 2007 06:23 Go to next message
oca_dwh
Messages: 3
Registered: October 2007
Junior Member
Hello everone,
Please heal me in resolving this issue.

I’m trying to fetch data thru this query

SELECT months_between (to_date (SUBSTR(A.DAILY_DOCTOR_SES,7,Cool), to_date (B.P_DOB_8))/12 AGE
FROM APPT_DETL A INNER JOIN REGISTERDB.PAT_DETL_1 B
ON (A.PATNO= B.PATNO)

The error message is popping up
“ORA-01861: literal does not match format string”
Re: months_between [message #279216 is a reply to message #279211] Wed, 07 November 2007 06:39 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
When using to_date it's a very good habit to add a formatting clause to it, like:
to_date('01-01-2007','dd-mm-yyyy')

I bet that the error you're hitting is a nice demonstration as to why this is such a good habit Wink
Re: months_between [message #279217 is a reply to message #279211] Wed, 07 November 2007 06:40 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Use explicit format string.
to_date(xyz,'DDMMRRRR')
By
Vamsi
Re: months_between [message #279221 is a reply to message #279217] Wed, 07 November 2007 06:56 Go to previous messageGo to next message
oca_dwh
Messages: 3
Registered: October 2007
Junior Member
Dear i'm still getting error but definitely not the same i was getting before adding the format to to_data

SELECT months_between (to_date (SUBSTR(A.DAILY_DOCTOR_SES,7,Cool,'DDMMRRRR'), to_date (B.P_DOB_8,'DDMMRRRR'))/12 AGE
FROM APPT_DETL A INNER JOIN REGISTERDB.PAT_DETL_1 B
ON (A.PATNO= B.PATNO)

this time the error is ORA-01843: not a valid month

your help in this would be appreciated
Re: months_between [message #279224 is a reply to message #279221] Wed, 07 November 2007 06:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SUBSTR(A.DAILY_DOCTOR_SES,7,8) doesn't look like DDMMRRRR.

MONTHS_BETWEEN function works with dates. So - make both arguments to be a DATE.

[Updated on: Wed, 07 November 2007 07:00]

Report message to a moderator

Re: months_between [message #279225 is a reply to message #279221] Wed, 07 November 2007 07:00 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Your parentheses are all over the place, this is where the issue lies (as well as storing dates as strings - but that's a whole different can of worms)

Just COMPLETELY ignore that I was talking absolute drivel there. Embarassed

[Updated on: Wed, 07 November 2007 07:02]

Report message to a moderator

Re: months_between [message #279231 is a reply to message #279221] Wed, 07 November 2007 07:39 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Next time, please add code tags to your code. With all those smilies in the statement, it's quite hard to comprehend what's going on. With code tags, the text stays simple text instead of turning into sunglassed-smiley... Mad

[Edit]Back on topic: you're substracting 8 characters from daily_doctor_ses starting from position 7, are you a 100% sure that there is a date there?? (BTW, what a weird column name...)

[Updated on: Wed, 07 November 2007 07:42]

Report message to a moderator

Previous Topic: Can view be called inside a view.
Next Topic: killing session
Goto Forum:
  


Current Time: Fri Dec 09 21:16:30 CST 2016

Total time taken to generate the page: 0.11730 seconds