Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I am trying to set up a query that is dynamic and the dates will constanntly roll forward. I am not having luck with the database, using sysdate. It keeps telling me that i need to put in a specific date with hours and minutes. Is there any other syntax I can use to query for data for every week? I'd like to do something like sysdate-6, so I can schedule the reload every week.
SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where "WORK_WEEK_ENDING_DATE" BETWEEN TO_DATE ('3-feb-2014','dd-mon-yyyy')- 6
AND TO_DATE ('3-feb-2014','dd-mon-yyyy')
and hour_type in ('OT1','OT2') ;
Create variables
let vStartDate =date( today() - 6);
let vEndDate = today();
Then use it in your load statement
SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where "WORK_WEEK_ENDING_DATE" BETWEEN $(vStartDate) AND $(vEndDate)
and hour_type in ('OT1','OT2') ;
database sql server (103 is for dd/mm/yyyy in convert function)
let vStartDate =date((today() - 6), 'DD/MM/YYYY');
let vEndDate = date(today(), 'DD/MM/YYYY');
trace $(vStartDate) $(vEndDate);
Table:
SQL SELECT *
FROM ENG."QTIME_SOURCE"."QTIME_SOURCE_13MONTH"
where "WORK_WEEK_ENDING_DATE" between convert(datetime,'$(vStartDate)',103) and convert(datetime,'$(vEndDate)',103)
and hour_type in ('OT1','OT2') ;