Using DATEADD or GETDATE in SQL Query in Qlikview

[AyCe1082]

Hi, I am trying to do an SQL query where load.LAST_UPDATED is within 7 days of the current date. I have seen many posts where people wanted the QV version of this but I need to have the SQL version work in the statement itself during a load. I keep getting invalid identifier errors or missing right parenthesis errors.

I have tried  

load.LAST_UPDATE < DateAdd(m, -7, GetDate()) 

load.LAST_UPDATE < GetDate()-7

and also tried putting the second half of each of those lines in extra parenthesis. Nothing seems to work in Qlikview.

[JustinDallas]

We would have to see the entire SQL LOAD statement.

[AyCe1082]

No problem. I don't think there is anything too sensitive here.

Fixtures:
SQL SELECT 
	sched.game_code "PK_gameId",
	sched.league_id "PK_leagueId",
	sched.season_id "PK_seasonId",  
	sched.game_date_ct "FIXTURE_gameDateTime",
	sched.home_team_name,
	sched.away_team_name,
	field.field_length_live,
	field.field_width_live,
	field.periods,
	load.LAST_UPDATE,
        DateAdd(m, -3, GetDate())
		
	FROM customer_data.cd_soccer_schedule sched,
	customer_data.cd_soccer_games field,
	commercial.SOCCER_GAME_LOADED load
	
	WHERE   sched.season_id  IN (201949,201939)
	AND     sched.game_code = field.game_code
	AND 	load.table_type_id = 4
	AND 	load.GAME_CODE = sched.GAME_CODE
	AND 	load.GAME_CODE = field.GAME_CODE
	AND     load.LAST_UPDATE < DateAdd(m, -7, GetDate())
	AND 	load.LOADED_STATUS = 1;

The final statement will be larger and pull a lot of data. LAST_UPDATE is in the format DD/MM/YYYY hh:mm:ss AM/PM

[rubenmarin]

Hi, you can create a variable using Qlikview syntax to set the date you want to start load, and use this variable in Sql statement using $(varName), like:

SET vStartDate = Date(Today()-7);  // substracts 7 days from today

SQL SELECT ... 

 AND load.LAST_UPDATE < '$(vStartDate)'

In your current sql statement  I think you need to use 'd' instead of 'm' 

DateAdd(d, -7, GetDate())

[AyCe1082]

Hi, thanks for the suggestion. 

I get anerror when I try it. It seems to be the variable isn't working during the load.

SQL##f - SqlState: S1000, ErrorCode: 1858, ErrorMsg: [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected

Fixtures:
SQL SELECT 
		sched.game_code "PK_gameId",
		sched.league_id "PK_leagueId",
		sched.season_id "PK_seasonId",  
		sched.game_date_ct "FIXTURE_gameDateTime",
		sched.home_team_name,
		sched.away_team_name,
		field.field_length_live,
		field.field_width_live,
		field.periods
		
	FROM customer_data.cd_soccer_schedule sched,
	customer_data.cd_soccer_games field,
	commercial.SOCCER_GAME_LOADED load
	
	WHERE   sched.season_id  IN (201949,201939)
	and     sched.game_code = field.game_code
	and 	load.table_type_id = 4
	AND 	load.GAME_CODE = sched.GAME_CODE
	AND 	load.GAME_CODE = field.GAME_CODE
	AND     load.LAST_UPDATE  < 'Date(Today()-7)'
	AND 	load.LOADED_STATUS = 1

I also tried changing SET to LET and get a different error:

SQL##f - SqlState: S1000, ErrorCode: 1843, ErrorMsg: [Oracle][ODBC][Ora]ORA-01843: not a valid month

Fixtures:
SQL SELECT
sched.game_code "PK_gameId",
sched.league_id "PK_leagueId",
sched.season_id "PK_seasonId",
sched.game_date_ct "FIXTURE_gameDateTime",
sched.home_team_name,
sched.away_team_name,
field.field_length_live,
field.field_width_live,
field.periods

FROM customer_data.cd_soccer_schedule sched,
customer_data.cd_soccer_games field,
commercial.SOCCER_GAME_LOADED load

WHERE sched.season_id IN (201949,201939)
and sched.game_code = field.game_code
and load.table_type_id = 4
AND load.GAME_CODE = sched.GAME_CODE
AND load.GAME_CODE = field.GAME_CODE
AND load.LAST_UPDATE < '04/07/2020 12:00:00 AM'
AND load.LOADED_STATUS = 1

I also get the not a valid month error with 

AND TO_DATE( load.LAST_UPDATE, 'DD/MM/YYYY') < '$(vStartDate)'

[rubenmarin]

Hi, yes sorry, it should be with LET.

Have you tried?:

AND load.LAST_UPDATE < TO_DATE($(vStartDate),'DD/MM/YYYY')

[AyCe1082]

Unfortunately it just gives this error:

SQL##f - SqlState: S1000, ErrorCode: 1858, ErrorMsg: [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected

Fixtures:
SQL SELECT
sched.game_code "PK_gameId",
sched.league_id "PK_leagueId",
sched.season_id "PK_seasonId",
sched.game_date_ct "FIXTURE_gameDateTime",
sched.home_team_name,
sched.away_team_name,
field.field_length_live,
field.field_width_live,
field.periods

FROM customer_data.cd_soccer_schedule sched,
customer_data.cd_soccer_games field,
commercial.SOCCER_GAME_LOADED load

WHERE sched.season_id IN (201949,201939)
and sched.game_code = field.game_code
and load.table_type_id = 4
AND load.GAME_CODE = sched.GAME_CODE
AND load.GAME_CODE = field.GAME_CODE
AND load.LAST_UPDATE < TO_DATE(04/07/2020,'DD/MM/YYYY')
AND load.LOADED_STATUS = 1

[AyCe1082 · Accepted solution]

This is what worked in the end:

LET vStartDate = Date(Today()-3,'DD-MMM-YYYY'); // substracts 7 days from today

while using TRUNC() in the where clause in the SQL load.

Apparently oracle did not like the date format and would only work if I changed it to the format 04-Jul-2020.