Home » SQL & PL/SQL » SQL & PL/SQL » Can i do this in a Single query (Oracle 9i)
Can i do this in a Single query [message #341988] Thu, 21 August 2008 00:42 Go to next message
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 #341989 is a reply to message #341988] Thu, 21 August 2008 00:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

yes, no, maybe

if you have correct answer, what is the problem?
Re: Can i do this in a Single query [message #341992 is a reply to message #341988] Thu, 21 August 2008 00:49 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Is it not
todate - fromdate

Or am I missing something here ?

Regards

Raj
icon6.gif  Re: Can i do this in a Single query [message #341995 is a reply to message #341992] Thu, 21 August 2008 00:56 Go to previous messageGo to next message
priyamalhotra
Messages: 43
Registered: July 2006
Location: none
Member

Hi,

You can make use of MONTHS_BETWEEN.

If you have another methods to do the same. Share it with us, which might be useful to all of us. You can share your existing / tested procedures etc.

Thanks,
Priya.

Re: Can i do this in a Single query [message #341996 is a reply to message #341988] Thu, 21 August 2008 00:57 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> Or am I missing something here ?

My guess is that the output shall contain one row for each month in the interval.
In this case, you can use of the row generator techniques.
Re: Can i do this in a Single query [message #342016 is a reply to message #341996] Thu, 21 August 2008 01:37 Go to previous messageGo to next message
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 #342021 is a reply to message #342016] Thu, 21 August 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Copy and paste your session.

Quote:
to_date(sysdate,'dd/mm/rrrr')

This is a bug. sysdate IS a date you can't convert it to a date:
SQL> select to_date(sysdate,'dd/mm/rrrr') from dual;
select to_date(sysdate,'dd/mm/rrrr') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Regards
Michel
Re: Can i do this in a Single query [message #342023 is a reply to message #342021] Thu, 21 August 2008 01:53 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 21 12:21:39 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: scott/tiger@primas

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> select to_date(sysdate,'dd/mm/rrrr') from dual;

TO_DATE(S
---------
21-AUG-08


Its working
Re: Can i do this in a Single query [message #342026 is a reply to message #342023] Thu, 21 August 2008 01:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #342032 is a reply to message #341996] Thu, 21 August 2008 02:13 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
> But want to get 6 more rows because diff between fromdate and todate is seven
flyboy wrote on Thu, 21 August 2008 07:57
My guess is that the output shall contain one row for each month in the interval.
In this case, you can use of the row generator techniques.

I am still not sure, if you want this, but you may start investigating the link I posted.

Regarding TO_DATE on DATEs, you may be interested in this thread: http://www.orafaq.com/forum/m/325150/96705/#msg_325150

[Edit: Added first quote from original poster]

[Updated on: Thu, 21 August 2008 02:15]

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 Go to previous messageGo to next message
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 #342047 is a reply to message #341996] Thu, 21 August 2008 03:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You certainly can read people mind Smile

Regards

Raj
Re: Can i do this in a Single query [message #342052 is a reply to message #342047] Thu, 21 August 2008 03:22 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Yes , So i will try it myself Sad

[Updated on: Thu, 21 August 2008 03:23]

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 Go to previous messageGo to next message
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 #342055 is a reply to message #342054] Thu, 21 August 2008 03:30 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

OK Sir
Re: Can i do this in a Single query [message #342059 is a reply to message #342047] Thu, 21 August 2008 03:40 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
S.Rajaram wrote on Thu, 21 August 2008 10:07
You certainly can read people mind Smile

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.
Previous Topic: Create a trigger in a procedure
Next Topic: Oracle 10 G
Goto Forum:
  


Current Time: Sat Nov 09 13:04:05 CST 2024