Filter by SysDate not working

[sirnose1]

I have an SQL query that loads data from a link server that returns Oracle data but I am having trouble filtering by sysdate. When I dont filter all data is returned but I just need the data for the previous day. What am I doing wrong here?

select S.* from openquery(LOTOS, 'SELECT RET_CD, GM_CD, GM_VAR, DRAW_CD, LOTOS_CD, GRS_AMN, CMS, CPNS, DT FROM LOTOSMIS.RET_DAILY WHERE RET_CD=14043 and DT = SYSDATE-4') S

 

[Andrzejek]

When you ask for SYSDATE: [blue][tt] Select sysdate from dual [/tt][/blue] you get: 8/12/2020 7:08:46 AM

You said:
"I just need the data for the previous day", but your select statement says: [tt]... WHERE RET_CD = 14043 and DT = [blue]SYSDATE-4[/blue][/tt], so what you actually doing is subtracting 4 days from current date and time value.

In other words you are saying: ... AND DT = 8/[red]8[/red]/2020 [red]7:08:46 AM[/red]


---- Andy

There is a great need for a sarcasm font.

 

[carp]

Try something like

SELECT RET_CD, GM_CD, GM_VAR, DRAW_CD, LOTOS_CD, GRS_AMN, CMS, CPNS, DT 
FROM LOTOSMIS.RET_DAILY 
WHERE RET_CD=14043 
and DT [COLOR=#EF2929]LIKE[/color] SYSDATE-4

Of course, as Andy points out, this is going to give you data from four days ago instead of the stated requirement of one day. But fixing that is left as an exercise for the developer.

 

[Andrzejek]

To get just today's date you may do:
[tt]SELECT TRUNC(SYSDATE) AS MYDATE FROM DUAL[/tt]
So. if you field DT does not have Time part (i.e. time is 00:00:000 - midnight), your statement could be:
[tt]
select S.* from openquery(LOTOS,
'SELECT RET_CD, GM_CD, GM_VAR, DRAW_CD, LOTOS_CD, GRS_AMN, CMS, CPNS, DT
FROM LOTOSMIS.RET_DAILY
WHERE RET_CD=14043 and
DT = [BLUE]TRUND(SYSDATE) - 1[/BLUE]') S
[/tt]
If you do have time portion, try:
[tt]... And DT BETWEEN TRUND(SYSDATE) - 1 AND TRUND(SYSDATE)[/tt]


---- Andy

There is a great need for a sarcasm font.