finding max year in the data

Database
Enthusiast

finding max year in the data

Hi Friends i am new to terada can any one help to write a query fro extracting only the values which are pertains to current year by using start date

for your understang i have kept some sample data as below

 

Thanks,

Ram

 

ACCOUNT_ID PRODUCT_CATALOG_ID PREV_FORMULARY_STATUS CURR_FORMULARY_STATUS START_DATE END_DATE
123,281 978 ? Rejected 1/1/2000 12/31/9999
540,293 501 ? In Progress 1/1/2000 12/31/9999
2,342,428 1,559 Blacklist Rejected 1/17/2017 12/31/9999
2,004,636 1,437 ? In Progress 1/1/2000 1/18/2017
757,466 229 ? Not Applicable 1/1/2000 12/31/9999
2,094,730 1,517 ? Rejected 1/1/2000 12/31/9999
1,861,238 1,695 ? In Progress 1/1/2000 1/13/2017
2,320,018 1,695 ? In Progress 1/1/2000 12/31/9999
2,004,636 1,472 ? Regional Committee 1/1/2000 12/31/9999
2,094,730 1,639 ? Rejected 1/1/2000 1/18/2017

4 REPLIES
Tourist

Re: finding max year in the data

Select * from table

where extract(year from current_date)=extract(year from start_date);

Junior Contributor

Re: finding max year in the data

Instead of applying a function on START_DATE better do the calculation only on CURRENT_DATE:

 

where START_DATE
between TRUNC(CURRENT_DATE, 'YY') -- Jan 1st current year
    and ADD_MONTHS(TRUNC(CURRENT_DATE, 'YY'),12)-1 -- Dec 31st current year

 

Enthusiast

Re: finding max year in the data

Thanks,

 

its working fine for me Thanks for your help

another question: how we can have next 13 months i mean current+next 12 months data

Junior Contributor

Re: finding max year in the data

First day of the current month: TRUNC(CURRENT_DATE, 'MM')

Last day of the nth month from now: LAST_DAY(ADD_MONTHS(CURRENT_DATE,13))