How to create a dynamic date.

[#1]

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')  ;

[iktrayanov]

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')  ;

[maxgro]

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')  ;