Display date in Oracle Toad

50 pts.
Tags:
Oracle
Oracle Database
TOAD
Toad Data Modeler
how to display all the months between two dates.my fromdate format is 'mon-yyyy' and todate format is 'dd-mon-yyyy'.plz sugest

Software/Hardware used:
oracle,toad

Answer Wiki

Thanks. We'll let you know when a new response is added.

Why not to use the Months_Between function:

Firstly you should visit <a href=”http://www.sql-performance-tuning.com/oracle-sql-functions/”>this</a> website, where you will find complete list of Oracle sql functions and <a href=”http://www.sql-performance-tuning.com/oracle-date-format/”>this</a> website where you will find complete list of Oracle date format elements. This should also help you for your future work.

<b>SQL function MONTHS_BETWEEN</b>

The MONTHS_BETWEEN function calculates the number of months between two dates. When the two dates have the same day component or are both the last day of the month, then the return value is a whole number. Otherwise, the return value includes a fraction that considers the difference in the days based on a 31-day month. The return value is positive when the first date is later than the second date, and negative when the first date is earlier than the second date.

You can take the “from” date and either make it the 1st of that month, or make it the same day as the “to” date, depending on the desired result. If you are setting the “from” day to be the same as the “to” day, you need to handle the special case of when the “to” date is in the range 29-31, since the “from” month may not contain that day.

If the day is the same in both the “from” and “to” dates (or if both are the last day of the respective month), the result will be an integer number of months. If the “from” day is not the same as the “to” day, the result will be a number like “xx.yy”, where “xx” will be the number of full months, and “yy” will be the fractional number of days (assuming a 31-day month = 1.0).

Since Months_Between accepts both datetime objects and text strings that are interpretable as dates, you can use your input formats with some simple string manipulation, or you can convert them to dates, e.g.
<pre>nBetween = Months_Between(todate, ’01-’ || fromdate);</pre>

Note that Months_Between returns a positive result when the 1st argument is a “later” date than the 2nd argument.

KCCrosser – Sorry for the above. That is what happens when you skim the question and read some answers without double-checking the real question…

Assumptions: You have a “FROM_DATE” value in the form “mmm-yyyy” and a “TO_DATE” value in the form “dd-mmm-yyyy”. You want to retrieve all records from a table that includes a date/time field (call it “RECORD_DATE”) where that date/time field falls between the 1st day of the FROM_DATE month and the TO_DATE.

Note – possible options that you need to consider: Do you want to include only months prior to the 1st day of the TO_DATE month? Do you want to include records for a partial month up to just before the actual date of TO_DATE? Do you want to include records that have a date/time falling anywhere on the actual day of TO_DATE? Do you want to include records through the end of the month in which TO_DATE falls?

Assuming that you want records up to just before TO_DATE (i.e., records up until 23:59:59.99999… on the day before TO_DATE), then the simple solution is to first convert the FROM_DATE and TO_DATE values into valid date/times and then use those in the query.
Also – minor date/time trick – it is usually best to formulate date/time queries using a where clause of the form:
where RECORD_DATE >= FROM_DATE and RECORD_DATE < TO_DATE
Why? Because there is no such thing as the “last moment in time on day xxx”, but there is a value 00:00:00.0 that is the absolute start of a new date.

<pre>
declare
sFROM_DATE varchar(255) := ‘Jan-2010′; — contains ‘mmm-yyyy’
sTO_DATE varchar(255) := ’17-Aug-2010′; — contains ‘dd-mmm-yyyy’
dtFROM_DATETIME datetime;
dtTO_DATETIME datetime;
begin
– force 1st day of month and convert to datetime value
dtFROM_DATETIME := to_date(’01-’ || sFROM_DATE, ‘dd-mon-yyyy’);
– convert to date/time, then force to 00:00:00 of “next” day
– Oracle represents date/time values where the integer part is a day, and the fractional
– part is the hours/minutes/seconds – adding 1 gets us into the next day, and truncating
– yields the 00:00:00 point of that day
dtTO_DATETIME := trunc(to_date(sTO_DATE, ‘dd-mon-yyyy’) + 1);
– now you can get the records between the dates (inclusive) with:
select …
from …
where …
and RECORD_DATE >= dtFROM_DATE
and RECORD_DATE < dtTO_DATE;

</pre>

If you wanted only records before the TO_DATE, then the “+1″ and “trunc” parts aren’t needed.
If you wanted records only to the end of the prior month, a simple technique would be to replace the day part of the TO_DATE with “01″:

<pre>
dtTO_DATETIME := to_date(’01′ || substr(sTO_DATE,3), ‘dd-mon-yyyy’);
</pre>

If you want to include all the month in which TO_DATE falls, I would use the previous statement to get the 1st day of the month in which it falls, then use ADD_MONTHS to get the next month:

<pre>
dtTO_DATETIME := ADD_MONTHS(dtTO_DATETIME, 1);
</pre>

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Could you please clarify your question a little ? An example would be good. Do you want to get the months names or the records whose date is included in the given time period ?
    69,475 pointsBadges:
    report
  • Meandyou
    A very interesting question ... display months between two dates ... very interesting indeed. (I am sort of curious as to the reason for this.) date math is tricky - when you subtract ending date from beginning date you do NOT get a date. You get a duration. if I subtract March (month 3) from January (month 1) I get a duration of 2 (2 months). if I subtract November (month 11) from May (month 5) I get a duration of 6 (6 months).
    so the result set would end up as 
      month name of beginning month
      recursive (month name of (previous month + 1) )
        until duration 
    
    If I had more time today I might play with this. Unfortunately, I don't.
    5,220 pointsBadges:
    report
  • Darryn
    Not 100% what you are trying to do here, but date handling can easily be done with a date_dimension table. For your starter requirements, you would have 4 columns: lookup_date (date) cal_month varchar2(3) month_num number cal_year number Populate the table (easiest using a spreadsheet then import). Then you can query the table as: select distinct cal_month from m_date_dimension where report_date >= (select max(lookup_date)+1 from date_dimension where cal_month||'-'||cal_year = :fromdate) and lookup_date <= :todate This will produce a list of the months, usually in alphabetical order, so you might want to do something like: select distinct month_num, cal_month||'-'||cal_year from m_date_dimension where report_date >= (select max(lookup_date)+1 from date_dimension where cal_month||'-'||cal_year = :fromdate) and lookup_date <= :todate order by cal_year, month_num This will order it properly according to calendar years and months between the 2 dates.
    765 pointsBadges:
    report
  • Darryn
    Sorry, the queries should be: select distinct cal_month from date_dimension where lookup_date >= (select max(lookup_date)+1 from date_dimension where cal_month||’-'||cal_year = :fromdate) and lookup_date <= :todate and select distinct month_num, cal_month||’-'||cal_year from date_dimension where lookup_date >= (select max(lookup_date)+1 from date_dimension where cal_month||’-'||cal_year = :fromdate) and lookup_date <= :todate order by cal_year, month_num
    765 pointsBadges:
    report
  • Deb4u
    [...] Deb4u needed help creating a display date in Oracle TOAD. Kccrosser, Meandyou, and Darryn gave it a [...]
    0 pointsBadges:
    report
  • sulica
    Exactly that I was looking for. Thank you. I also found websites for Oracle date time functions: http://www.sql-performance-tuning.com/oracle-sql-functions/ and website for Oracle date format: http://www.sql-performance-tuning.com/oracle-date-format/ I think this should help you with oracle dates.
    235 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following