CODEWITHSUNDEEP
sqloracle-databasedatetimedynamic
DavidW
DavidW

Reputation: 11

Oracle SQL Dynamic Start and End Date

I want to have dynamic Oracle start and end date SQL that will return the start_date that is <= 2019-12-31 when Sys_Date is >= Nov 16, 2019. The YYYY portion of the criteria needs to increase by one year, every Nov 16. For example, when Sys_Date is >= Nov. 16, 2020, the query will return records having a start_date <= 2020-12-31 and on Nov. 16, 2021 the query will return records having a start date <= 2021-12-31.

I'm using Alteryx and the IN-db tools. I'm briefly familiar with these Oracle functions, SysDate, To_Date, Trunc, Add_Months, and Next_Day.

There is an end_Date criteria that needs to advance every Nov 16 by one year as well. As of today, Jan 13, 2020, The current end_date being returned is >= 2019-01-01. On Nov 16, 2020 the end_Date returned will be >= 2020-01-01.

This working Oracle SQL requires annual update on Nov 16.

"START_DATE" <= to_date('2019-12-31','yyyy-mm-dd') and "END_DATE" >= to_date('2019-01-01','yyyy-mm-dd')

Below is successful Alteryx code using a DateTimeNow function (not Oracle) to obtain/select/pass the desired dates, but that isn't helping me with Oracle SQL.

Below is code (not Oracle) used in the Alteryx application to query certain file dates using datetimeToday function.

([FileDt] > Datetimeformat(DatetimeAdd(DateTimeToday(),-1,"year"),'%Y-01-01')) 
AND    // pass [FileDt] > Jan 1 of current year -1 year
ToDate(DateTimeNow())< ToDate(tostring(DateTimeYear(DateTimeNow()))+"-11-15") 
  //pass files when today is < Nov 15 current year
OR
([FileDt] >= ToDate(tostring(DateTimeYear(DateTimeNow()))+"-01-01") 
AND   // pass [FileDt] >= Jan 1 of current year
ToDate(DateTimeNow())>= ToDate(tostring(DateTimeYear(DateTimeNow()))+"-11-15")) 
  // pass files when today is >= Nov 15 Current Year

Upvotes: 1

Views: 1820

Answers (2)

DavidW
DavidW

Reputation: 11

Thank you for the input. The code below is used to create a StartDate that meets my requirements: 

CASE
    WHEN SYSDATE BETWEEN 
                 TO_DATE ('1116' || (EXTRACT (YEAR FROM SYSDATE)), 'MM/DD/YYYY')
             AND TO_DATE ('1231' || (EXTRACT (YEAR FROM SYSDATE)), 'MM/DD/YYYY')
    THEN TO_DATE ('1231' || EXTRACT (YEAR FROM SYSDATE), 'MM/DD/YYYY')

    WHEN SYSDATE BETWEEN 
                 TO_DATE ('0101' || (EXTRACT (YEAR FROM SYSDATE)), 'MM/DD/YYYY')
             AND TO_DATE ('1115' || (EXTRACT (YEAR FROM SYSDATE)), 'MM/DD/YYYY')
    THEN TO_DATE ('1231' || EXTRACT (YEAR FROM SYSDATE)-1, 'MM/DD/YYY')

    ELSE NULL
END

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

Could be something like this. Maybe this is not 100% correct but you should get an idea how it works.

WHERE start_date <= 
   CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MMDD')) < 1116 THEN 
      ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 12)-1
   ELSE 
      ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 24)-1
   END

Upvotes: 0

Related Questions