Home » Developer & Programmer » Reports & Discoverer » Birthday Report. (Discoverer v10)
Birthday Report. [message #460142] Wed, 09 June 2010 20:24 Go to next message
meksta
Messages: 4
Registered: June 2010
Location: Sydney
Junior Member
I need to create a report that shows only the DD-MON of a birthday while ignoring the year and then having the output in DATE form so I can sort chronologically. My input is in DD-MON-YYYY format.

I have used Date_Trunc to pull out the month and day but the 29-Feb dates are giving me the ORA-1839 error message.

Report message to a moderator

Re: Birthday Report. [message #460150 is a reply to message #460142] Wed, 09 June 2010 22:16 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
Date_Trunc
Is it a Oracle built-in?Quote:
having the output in DATE form so I can sort chronologically.
Why do you need DATE output to sort chronologically?
Why can't you do a to_char and order by on the column?

By
Vamsi

Report message to a moderator

Re: Birthday Report. [message #460160 is a reply to message #460150] Thu, 10 June 2010 00:07 Go to previous messageGo to next message
meksta
Messages: 4
Registered: June 2010
Location: Sydney
Junior Member
Sorry, I used the EUL_DATE_TRUNC function.

I tried using to_char but found that results were not in date form and hence were sorting numerically or alphabetically.

Report message to a moderator

Re: Birthday Report. [message #460161 is a reply to message #460150] Thu, 10 June 2010 00:09 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd rather find "DD-MON" with the TO_CHAR function (being puzzled, just like Vamsi, what might DATE_TRUNC be?):
SQL> select to_char(sysdate, 'dd-mon') from dual;

TO_CHA
------
10-jun

SQL>

On the other hand, saying thatQuote:
My input is in DD-MON-YYYY format
implies ... what exactly? Do you store dates as characters? What is "input" here? User's parameter, or data found in a table?

If you could provide a simple test case so that we would see it in more details, it would be easier to suggest a possible solution.

Report message to a moderator

Re: Birthday Report. [message #460162 is a reply to message #460161] Thu, 10 June 2010 00:16 Go to previous messageGo to next message
meksta
Messages: 4
Registered: June 2010
Location: Sydney
Junior Member
The Data is in a table. Sample 02-JUN-1983.

Report message to a moderator

Re: Birthday Report. [message #460169 is a reply to message #460162] Thu, 10 June 2010 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's all you have to say?
Meaning: that's still too few information.

[Updated on: Thu, 10 June 2010 00:51]

Report message to a moderator

Re: Birthday Report. [message #460196 is a reply to message #460169] Thu, 10 June 2010 02:16 Go to previous messageGo to next message
meksta
Messages: 4
Registered: June 2010
Location: Sydney
Junior Member
Right So I have tried what you suggested
SQL> select to_char(sysdate, 'dd-mon') from dual;

And it brings the correct form. But I then need this colum sorted in order. When I sort it brings results
Currently
01-apr
01-aug
01-dec
05-apr
29-feb

Instead of
29-feb
01-apr
05-apr
01-aug
01-dec

Currently the table hold over 10,000 entries. How is it possible to have it sorted the way I want?

Report message to a moderator

Re: Birthday Report. [message #460198 is a reply to message #460196] Thu, 10 June 2010 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Records won't be sorted just because you want them to. You have to explicitly say it. In Oracle, we use ORDER BY clause.
select some_column
from your_table
ORDER BY <column or list of columns you'd want the result to be sorted by>

Report message to a moderator

Re: Birthday Report. [message #460235 is a reply to message #460198] Thu, 10 June 2010 04:55 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
The problem you've got is that for oracle to order a date as a date it needs a full date - day/month/year at least.
I'm guessing that you're extracting the month and day and then converting back to date to order.
That doesn't work with 29-Feb because if you don't specify a year oracle assumes the current year, and this year isn't a leap year.
I can think of two work arounds:
1) order by to_char(date, 'MMDD') - this'll sort as character but give the right result:
SQL> WITH DATA AS (SELECT to_date('01-apr-2001', 'DD-mon-yyyy') birthday FROM dual UNION
  2                SELECT to_date('01-aug-2004', 'DD-mon-yyyy') FROM dual UNION
  3                SELECT to_date('01-dec-2009', 'DD-mon-yyyy') FROM dual UNION
  4                SELECT to_date('05-apr-2007', 'DD-mon-yyyy') FROM dual UNION
  5                SELECT to_date('29-feb-2008', 'DD-mon-yyyy') FROM dual)
  6  SELECT to_char(birthday, 'DDMON') 
  7  FROM DATA
  8  ORDER BY to_char(birthday, 'MMDD');

TO_CH
-----
29FEB
01APR
05APR
01AUG
01DEC

SQL>


2: When converting back to a date put in a year that's a leap year (doesn't matter which one):
SQL> WITH DATA AS (SELECT to_date('01-apr-2001', 'DD-mon-yyyy') birthday FROM dual UNION
  2                SELECT to_date('01-aug-2004', 'DD-mon-yyyy') FROM dual UNION
  3                SELECT to_date('01-dec-2009', 'DD-mon-yyyy') FROM dual UNION
  4                SELECT to_date('05-apr-2007', 'DD-mon-yyyy') FROM dual UNION
  5                SELECT to_date('29-feb-2008', 'DD-mon-yyyy') FROM dual)
  6  SELECT to_char(birthday, 'DDMON') 
  7  FROM DATA
  8  ORDER BY to_date(to_char(birthday, 'DDMON')||'2008', 'DDMONYYYY');

TO_CH
-----
29FEB
01APR
05APR
01AUG
01DEC

SQL>

Report message to a moderator

Re: Birthday Report. [message #460236 is a reply to message #460235] Thu, 10 June 2010 04:59 Go to previous message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
And next time you've got a question post the full query and results as well as expected results.
It saves on us having to guess what the issue is and will get you an answer faster.
I suggest you have a read of the forum guide

Report message to a moderator

Previous Topic: how to use the System parameter
Next Topic: how create report xls
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Nov 27 05:46:40 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.