Home » SQL & PL/SQL » SQL & PL/SQL » Days between
Days between [message #408409] Tue, 16 June 2009 02:43 Go to next message
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 #408411 is a reply to message #408409] Tue, 16 June 2009 02:48 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Pl;ease post a describe of the table that you are working with.
Re: Days between [message #408414 is a reply to message #408409] Tue, 16 June 2009 02:51 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 #408423 is a reply to message #408409] Tue, 16 June 2009 03:17 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member


Use Nested function.
try this

select TRUNC(TO_DATE(End_DATE,'DD-MM-YYYY') - TO_DATE(start_date,'DD-MM-YYYY'))
Re: Days between [message #408426 is a reply to message #408409] Tue, 16 June 2009 03:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 #408431 is a reply to message #408426] Tue, 16 June 2009 03:30 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP
day of month must be between 1 and last day of month

Obviously, users were creative while entering dates (or what they *thought* that might look like a date), such as 30th February or 12.30.2009 (dd.mm.yyyy) and similar. It is a mess which needs to be cleared up. Happy tyding!
Re: Days between [message #408458 is a reply to message #408409] Tue, 16 June 2009 05:22 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Am a guy , Laughing

Laughing

Also the error i got was the same i mentioned before .

I will check the details fo eth Dba nd post as soon i get it.

thanks aton for your help frnds
Re: Days between [message #408464 is a reply to message #408458] Tue, 16 June 2009 05:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
avrillavinge wrote on Tue, 16 June 2009 12:22
Am a guy

Are you sure?
Re: Days between [message #408466 is a reply to message #408458] Tue, 16 June 2009 05:49 Go to previous messageGo to next message
pablolee
Messages: 2836
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 #408467 is a reply to message #408464] Tue, 16 June 2009 05:51 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Quote:
avrillavinge wrote on Tue, 16 June 2009 12:22
Am a guy


Are you sure?
Quote:
Are you sure?

Laughing
Re: Days between [message #408469 is a reply to message #408464] Tue, 16 June 2009 05:57 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Littlefoot wrote on Tue, 16 June 2009 05:46
avrillavinge wrote on Tue, 16 June 2009 12:22
Am a guy

Are you sure?



Razz
Re: Days between [message #408471 is a reply to message #408409] Tue, 16 June 2009 06:00 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
i like her songs ...hence i used this username ..guys..

nothing else....
Re: Days between [message #408472 is a reply to message #408471] Tue, 16 June 2009 06:22 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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 #408478 is a reply to message #408409] Tue, 16 June 2009 06:44 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
Allright ....Smile

BTW my name is venugopal Smile
Re: Days between [message #408479 is a reply to message #408472] Tue, 16 June 2009 06:45 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

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, Very Happy
Previous Topic: how to migrate the encrypted data from one data base to another data base
Next Topic: How to Create Table with CLOB column (Size 1 GB Only)
Goto Forum:
  


Current Time: Fri Dec 09 09:36:59 CST 2016

Total time taken to generate the page: 0.21970 seconds