| Days between [message #408409] |
Tue, 16 June 2009 02:43  |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
i need to calculate the days between start date and end date
i am using the below formula
trunc( startdate,'dd')-trunc(enddate,'dd')
but it gives me the below error
Exception: DBD, ORA-01722: invalid number
State: N/A
can anyone give me the correct formula to find the days between the two dates .
Any help is appreciated
|
|
|
|
|
|
| Re: Days between [message #408414 is a reply to message #408409] |
Tue, 16 June 2009 02:51  |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
the startdate and end date are character types
anything else info u need pls let me know
what i need is only the days between start and end
for eg:
if i start of 10th june and end on 15th june then
i want teh diff ie 5 days .
Please advise
I even tried this but same error
trunc(to_date(End_DATE,'dd-mm-yyyy'),'dd')-trunc(to_date(Start_DATE,'dd-mm-yyyy'),'dd')
[Updated on: Tue, 16 June 2009 02:56] Report message to a moderator |
|
|
|
| Re: Days between [message #408416 is a reply to message #408414] |
Tue, 16 June 2009 02:56 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
1. I asked for a describe of your table, you didn't supply it. Granted you supplied the information that I actually was looking for, but you still did not do wjhat I asked. You will find that you will get answers much quicker if you provide the information that is actually asked of you.
2. Why on earth are your 'dates' being stored as strings. This is a very foolish idea.
3. Stop using IM speak. u = You, pls = Please,
4. You need to convert your strings into dates using the to_date function. Look up the syntax in the documentation (I know that you have been pointed to it many times before.)
|
|
|
|
| Re: Days between [message #408417 is a reply to message #408409] |
Tue, 16 June 2009 03:03 |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
My apologies for using IM.
Thanks for your feedback
the Db here is very strange and dates are stored in char.
i tried converting them to date and then trunc and subtract but again it throws error that this Query doesnt refernce a table
can yopu help
|
|
|
|
| Re: Days between [message #408419 is a reply to message #408417] |
Tue, 16 June 2009 03:10 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Not if you don't post what exactly you did and what exactly the error is.
( a FORMATTED copy and paste of the entire SQL*Plus session, including the error message )
|
|
|
|
| Re: Days between [message #408420 is a reply to message #408417] |
Tue, 16 June 2009 03:11 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
| Quote: | i tried converting them to date and then trunc and subtract but again it throws error that this Query doesnt refernce a table
|
You should post things you did in your sqlplus windows so that every one see the exact error.
Also storing dates in char data type is like shooting yourself in the foot.
|
|
|
|
|
|
| Re: Days between [message #408426 is a reply to message #408409] |
Tue, 16 June 2009 03:22 |
avrillavinge
Messages: 98
Registered: July 2007
|
Member |
|
|
Exception: DBD, ORA-01847: day of month must be between 1 and last day of month
State: N/A
am getting teh abover error
even after using the query suggested by seyed,
Please guide
|
|
|
|
| Re: Days between [message #408427 is a reply to message #408423] |
Tue, 16 June 2009 03:24 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
Clearly your crystal balls aren't quite up to scratch seyed456. Instead of just jumping in with both feet and getting the answer wrong, how about we let avril actually post what she has tried (even though it would appear that she is completely unwilling to do this for some strange reason)
|
|
|
|
| Re: Days between [message #408428 is a reply to message #408409] |
Tue, 16 June 2009 03:26 |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I would imagine that some of your data isn't a valid date. Which is way you should never store dates in character columns.
You're going to have to find the invalid dates and fix them.
|
|
|
|
| Re: Days between [message #408430 is a reply to message #408409] |
Tue, 16 June 2009 03:30 |
|
|
pabolee you are right,
i was used to do to make difference for table columns which mentioned on char.
for avril ,
please attach the snapshots of sqlplus and also include your scripts and make sure the code is formatted..
|
|
|
|
|
|
|
|
|
|
| Re: Days between [message #408466 is a reply to message #408458] |
Tue, 16 June 2009 05:49 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| avrillavinge wrote on Tue, 16 June 2009 11:22 | Am a guy
|
The reason that I assumed that you were female is your username. Avril is a girl's name in my neck of the woods.
|
|
|
|
|
|
|
|
|
|
| Re: Days between [message #408472 is a reply to message #408471] |
Tue, 16 June 2009 06:22 |
|
|
| avrillavinge wrote on Tue, 16 June 2009 06:00 | i like her songs ...hence i used this username ..guys..
nothing else....
|
ok .
no issues..Despite its not the place to put opposite sex names..
It makes a change in you not with us..
|
|
|
|
|
|
| Re: Days between [message #408479 is a reply to message #408472] |
Tue, 16 June 2009 06:45 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| seyed456 wrote on Tue, 16 June 2009 12:22 | Despite its not the place to put opposite sex names.
|
Says who? There is absolutely no reason as to why that should be the case. What does it matter?
|
|
|
|
| Re: Days between [message #408504 is a reply to message #408479] |
Tue, 16 June 2009 08:00 |
|
|
| pablolee wrote on Tue, 16 June 2009 06:45 | | seyed456 wrote on Tue, 16 June 2009 12:22 | Despite its not the place to put opposite sex names.
|
Says who? There is absolutely no reason as to why that should be the case. What does it matter?
|
Actually avril is he but people here were calling she...thats the case ..nothing wrong with that, 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
