Can i do this in a Single query [message #341988] |
Thu, 21 August 2008 00:42 |
rakeshramm
Messages: 175 Registered: September 2006 Location: Oracle4u.com
|
Senior Member |
|
|
I want to retrieve the number of days in each month between two date fields in a table .I got the result by using a procedure by using loops and all sorts of things but can get those output in a single query .If any one knows please help me .thanks in Advance
Table Structure
>desc tab1;
loginid varchar2(20);
fromdate date;
todate date;
|
|
|
|
|
|
|
Re: Can i do this in a Single query [message #342016 is a reply to message #341996] |
Thu, 21 August 2008 01:37 |
rakeshramm
Messages: 175 Registered: September 2006 Location: Oracle4u.com
|
Senior Member |
|
|
I am trying to do the above mentioned in a single query like this if any can help .Plz help me
select to_date(sysdate,'dd/mm/rrrr') todat,last_day(to_date('19/01/2008','dd/mm/yyyy')) lastday,
to_char(to_date('19/01/2008','dd/mm/yyyy'),'Month') mon,round(months_between(sysdate,to_date('19/01/2008','dd/mm/yyyy')),0) monbtw
,last_day(to_date('19/01/2008','dd/mm/yyyy'))-to_date('19/01/2008','dd/mm/yyyy') diff,
to_number(to_char(to_date('19/01/2008','dd/mm/yyyy'),'mm') )
,sysdate-to_date('19/01/2008','dd/mm/yyyy') difffrmtod,to_date('19/01/2008','dd/mm/yyyy') fromdate
from dual
But want to get 6 more rows because diff between fromdate and todate is seven
[Updated on: Thu, 21 August 2008 01:39] Report message to a moderator
|
|
|
|
|
Re: Can i do this in a Single query [message #342026 is a reply to message #342023] |
Thu, 21 August 2008 01:56 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The fact that it works for you does NOT prove it is correct.
The fact that it does NOT work for me prove it is wrong.
In addition, read the documentation you will see that TO_DATE first parameter is a string and not a date.
Now why it does not work for me and why it works for you?
Regards
Michel
[Updated on: Thu, 21 August 2008 01:57] Report message to a moderator
|
|
|
Re: Can i do this in a Single query [message #342031 is a reply to message #342026] |
Thu, 21 August 2008 02:12 |
rakeshramm
Messages: 175 Registered: September 2006 Location: Oracle4u.com
|
Senior Member |
|
|
Ok Sir I agree but my main problem is not this it is above mentioned
select sysdate todat,last_day(to_date('19/01/2008','dd/mm/yyyy')) lastday,
to_char(to_date('19/01/2008','dd/mm/yyyy'),'Month') mon,
round(months_between(sysdate,to_date('19/01/2008','dd/mm/yyyy')),0) monbtw
,last_day(to_date('19/01/2008','dd/mm/yyyy'))-to_date('19/01/2008','dd/mm/yyyy') diff,
to_number(to_char(to_date('19/01/2008','dd/mm/yyyy'),'mm') )
,sysdate-to_date('19/01/2008','dd/mm/yyyy') difffrmtod,to_date('19/01/2008','dd/mm/yyyy') fromdate
from dual
[Updated on: Thu, 21 August 2008 02:43] by Moderator Report message to a moderator
|
|
|
|
Re: Can i do this in a Single query [message #342039 is a reply to message #342031] |
Thu, 21 August 2008 02:44 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | Ok Sir I agree but my main problem is not this it is above mentioned
|
It is not currently YOUR main problem because you just want the solution of your homework but it WILL be your main problem in real life.
Way to find the solution has already been given by flyboy.
Also take care of:
Quote: | Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
|
Regards
Michel
[Updated on: Thu, 21 August 2008 02:56] Report message to a moderator
|
|
|
|
|
Re: Can i do this in a Single query [message #342054 is a reply to message #342052] |
Thu, 21 August 2008 03:27 |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
By all means please do it. You will learn a lot by doing it that way. If you are stuck come back to us with what you have tried so far and where you are having issues. Definitely somebody will point you in the right directions. But remember before you post next time please follow the guidelines.
Regards
Raj
|
|
|
|
Re: Can i do this in a Single query [message #342059 is a reply to message #342047] |
Thu, 21 August 2008 03:40 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
S.Rajaram wrote on Thu, 21 August 2008 10:07 | You certainly can read people mind
|
As I stated, this was just a guess based on this part of the first post:
Quote: | I got the result by using a procedure by using loops and all sorts of things
| , however I think that this description is quite exaggerated, as a single loop with some date arithmetic should suffice.
|
|
|