Hello all. How in PLSQL do I say I just want the year? This is what I want. In my WHERE clause I usually say PLAN.YEARENDDATE = '31-DEC-2002'. I want all the results where the year = 2002. How do I syntax that? I tried using the LIKE statement but that doesnt understand it for date I guess. Thanks all
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using Year Format in Query Help
- Thread starter ghost2
- Start date
- Thread starter
- #3
The only problem this might impose is if you have an index on PLAN.YEARENDDATE. If this is the case, then you might want to either create a function-based index on the column or recast your WHERE clause to
PLAN.YEARENDDATE BETWEEN '1-JAN-2002' AND '31-DEC-2002';
On the other hand, if you don't have an index on this column, then the above solution should work quite nicely.
If you want to be able to pass in an arbitrary year, you might want to either build the query dynamically or create a couple of functions:
CREATE FUNCTION first_of_year(p_year IN NUMBER) RETURNS DATE
IS
BEGIN
RETURN to_date('1-JAN-'BEGIN
RETURN to_date('1-JAN-'||to_char(p_year),'DD-MON-YYYY');
END;
Creating a companion function (last_of_year) would allow
you to change the WHERE clause to:
WHERE plan.yearenddate BETWEEN first_of_year(2002) and last_of_year(2002);
PLAN.YEARENDDATE BETWEEN '1-JAN-2002' AND '31-DEC-2002';
On the other hand, if you don't have an index on this column, then the above solution should work quite nicely.
If you want to be able to pass in an arbitrary year, you might want to either build the query dynamically or create a couple of functions:
CREATE FUNCTION first_of_year(p_year IN NUMBER) RETURNS DATE
IS
BEGIN
RETURN to_date('1-JAN-'BEGIN
RETURN to_date('1-JAN-'||to_char(p_year),'DD-MON-YYYY');
END;
Creating a companion function (last_of_year) would allow
you to change the WHERE clause to:
WHERE plan.yearenddate BETWEEN first_of_year(2002) and last_of_year(2002);
To include datetimes within 31.12.2002 the condition should be a bit "wider":
PLAN.YEARENDDATE BETWEEN to_date to_date('01.01.02','dd.mm.rr') AND to_date('01.01.03','dd.mm.rr')-1/(24*60*60)
Of course, it needs a bit more more writing than TO_CHAR or TRUNC (BTW, you may also use TRUNC(PLAN.YEARENDDATE,'y')=to_date('01.01.03','dd.mm.rr') ), but in some cases using index may speed up query significantly.
Though, if you query some realtime data (I mean not within hundreds of years), it may also NOT HELP, because of relatively large percentage of appropriate data.
Counting in most cases is more efficient with the use of index.
Regards, Dima
PLAN.YEARENDDATE BETWEEN to_date to_date('01.01.02','dd.mm.rr') AND to_date('01.01.03','dd.mm.rr')-1/(24*60*60)
Of course, it needs a bit more more writing than TO_CHAR or TRUNC (BTW, you may also use TRUNC(PLAN.YEARENDDATE,'y')=to_date('01.01.03','dd.mm.rr') ), but in some cases using index may speed up query significantly.
Though, if you query some realtime data (I mean not within hundreds of years), it may also NOT HELP, because of relatively large percentage of appropriate data.
Counting in most cases is more efficient with the use of index.
Regards, Dima
- Status
Similar threads
