anumoses
asked on
Oracle date question
If
to_char(:p_start_date, 'MM/DD/YYYY') > to_char(:p_end_date, 'MM/DD/YYYY') then
message(........)
( Stops the report)
End if;
I need help when the user runs the report between years like
start date- 01-jan-2013 to
end date- 01-jan-2014
Say the user has to run report always in the year range, ie between jan 2013 and dec 2013. How can I do that.
to_char(:p_start_date, 'MM/DD/YYYY') > to_char(:p_end_date, 'MM/DD/YYYY') then
message(........)
( Stops the report)
End if;
I need help when the user runs the report between years like
start date- 01-jan-2013 to
end date- 01-jan-2014
Say the user has to run report always in the year range, ie between jan 2013 and dec 2013. How can I do that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
First, should you be using to_char?
Assuming to_date is correct, I just make sure I get them in the right order and I don't have to worry about stopping the report:
Use MIN for the left side and MAX for the right side.
min(to_date(:p_start_date, 'MM/DD/YYYY'),to_date(:p_e nd_date, 'MM/DD/YYYY'))
and
max(to_date(:p_start_date, 'MM/DD/YYYY'),to_date(:p_e nd_date, 'MM/DD/YYYY'))
Assuming to_date is correct, I just make sure I get them in the right order and I don't have to worry about stopping the report:
Use MIN for the left side and MAX for the right side.
min(to_date(:p_start_date,
and
max(to_date(:p_start_date,
you mean the user can pick any year they want but they must start on Jan 1 and end on Dec 31?
if so, then change the prompt to only ask for the year. Don't make them enter extra values that only serve to add error conditions.
if so, then change the prompt to only ask for the year. Don't make them enter extra values that only serve to add error conditions.
Anumoses, you have got to start treating dates as dates and not characters if you are going to performa any kind of date math. With character data 12/31/2013 is greater than 01/31/2014, which results in inaccurate comparisons.
If you let the user just pick the year, you could "parse" it like this for your query needed in your report:
Apart from that, I totally agree with awking00: do NOT treat dates as strings in scenarios like yours!
select trunc(to_date('2013', 'yyyy'), 'Y') first_day_of_year,
add_months(trunc(to_date('2013', 'yyyy'), 'Y'), 12) - 1 last_day_of_year
from dual;
whereas '2013' should be replaced with the appropriate parameter ;-)Apart from that, I totally agree with awking00: do NOT treat dates as strings in scenarios like yours!
ASKER
you mean the user can pick any year they want but they must start on Jan 1 and end on Dec 31?
if so, then change the prompt to only ask for the year. Don't make them enter extra values that only serve to add error conditions.
I cant ask only year. Since the report has to be run on the day and month also. This gives the user a graph for the date range.
if so, then change the prompt to only ask for the year. Don't make them enter extra values that only serve to add error conditions.
I cant ask only year. Since the report has to be run on the day and month also. This gives the user a graph for the date range.
ASKER
The message I am trying to give the user is here
The start date and end date has to be within the same year range.
The start date and end date has to be within the same year range.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
see this article to explanations:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
if really you wanted to use TO_CHAR , then with a format like YYYYMMDD , YYYY-MM-DD , YYYY/MM/DD or the like, otherwise the "comparison" will not work according to the "values" of dates.
if you do DD/MM/YYYY or MM/DD/YYYY, it will first compar the months and days values before comparing the year values, as the resulting string is no longer a "date".
hope this clarifies