Search code examples
sqlnetezzaaginity

Variable dates in Netezza

I wish to define a start and end date for my SQL query in Netezza.

My code is re-run every quarter to pull the latest data and rather than changing the date fields in multiple locations in the code I wish to define a start and end date that can be re-used throughout the code.

I have been trying the following code to get the start and end date in Aginity but my code will not run

CREATE OR REPLACE PROCEDURE START_END_DATE_VARS()
  --SPECIFIC START_END_DATE_VARS
  LANGUAGE NZPLSQL 
  BEGIN
declare StartDate char;
declare EndDate char;
SELECT StartDate = TO_CHAR(last_day(add_months(now(),-1)),'YYYY-MM-DD') INTO StartDate;
SELECT EndDate = TO_CHAR(add_months(date_trunc('month', current_date),-35),'YYYY-MM-DD') INTO EndDate;
END

I would be very grateful if you could give me a steer in the right direction as to how to define these variables in Aginity/Netezza.

Solution

  • This works, but if you wanna use those dates you should insert them into some table or so on.. 

    CREATE OR REPLACE PROCEDURE START_END_DATE_VARS()
RETURNS CHARACTER VARYING(ANY)
LANGUAGE NZPLSQL AS
BEGIN_PROC

DECLARE
StartDate   CHAR(10);
EndDate     CHAR(10);

BEGIN 
StartDate := (SELECT TO_CHAR(last_day(add_months(now(),-1)),'YYYY-MM-DD'));
EndDate := (SELECT TO_CHAR(add_months(date_trunc('month', current_date),-35),'YYYY-MM-DD'));

RETURN('Start date: ' || StartDate ||' , End Date: ' ||EndDate );

END;
END_PROC;