I’m wanting to subtract days from date fields

Database
Enthusiast

I’m wanting to subtract days from date fields

Hi folks,

I have the below query that I'm tryin to run via BTEQ but it fails with following error:
Failure 5407 Invalid operation on an ANSI Datetime or Interval value.
Statement# 1, Info =0

However, it runs ok via SQL Assist.

Select username, event, logdate, logtime, logonsource
from (select logdate , cast (logtime as time) logtime, username, logonsource, event, (
case event
when 'bad password' then 3
when 'bad user' then 2
when 'login failed' then 1
else 0
end ) as eventsum, sum (eventsum) over (partition by username
order by logdate, logtime rows 3 preceding) as x
from dbc.logonoff
where event in('Logon Failed','Bad password','Bad user')
and logdate >= (current_date - INTERVAL '14' DAY)
and (username, logdate) in (
select username, logdate
from dbc.logonoff
where event in ('BAD PASSWORD','BAD USER','Logon Failed')) ) T1
where X =3
group by 2,1,3,4,5
order by 2 ;

Have even tried to modify the where clause to..."logdate >= (current_date - INTERVAL '14' DAY)" but get the same error. Any suggestions as to what function I could use to get this query running ?

Thanks in advance.
3 REPLIES
Enthusiast

Re: I’m wanting to subtract days from date fields

On the last comment, meant to say even tried to change the where clause to..."and (current_date - logdate) <= 14" but getting same error, Failure 5407 Invalid operation on an ANSI Datetime or Interval value
Junior Supporter

Re: I’m wanting to subtract days from date fields

Hi.

The issue here is logtime, which happens to be FLOAT datatype (!!).

select Logtime, type(LogTime) from DBC.LogOnOff sample 1;

*** Query completed. 1 row found. 2 columns returned.
*** Total elapsed time was 1 second.

LogTime Type(LogTime)
----------- ------------------
10:53:16.13 FLOAT

Thus, the CAST (logtime as time) fails.

But:

Select username,
event,
logdate,
logtime,
logonsource
from ( select logdate ,
cast(cast(LogTime as varchar(11)) as time(2) ) logtime, -- Note the CAST!!!
username,
logonsource,
event,
( case event
when 'bad password' then 3
when 'bad user' then 2
when 'login failed' then 1
else 0
end ) as eventsum,
sum (eventsum) over (partition by username order by logdate, logtime rows 3 preceding) as x
from dbc.logonoff
where event in ('Logon Failed','Bad password','Bad user')
and logdate >= (current_date - INTERVAL '14' DAY)
and (username, logdate) in ( select username,
logdate
from dbc.logonoff
where event in ('BAD PASSWORD','BAD USER','Logon Failed')
)
) T1
where X =3
group by 2,1,3,4,5
order by 2 ;

*** Query completed. 2 rows found. 5 columns returned.
*** Total elapsed time was 1 second.

UserName Event LogDate logtime LogonSource
------------------------------ ------------ -------- ----------- --------------------------------------------------------------------------------------------------------------------------------
XXXX Bad Password 11/01/05 15:10:30.74 (TCP/IP) Blah, Blah, Blah...
YYYYYYY Bad Password 11/01/17 10:57:26.84 (TCP/IP) Blah, Blah, Blah...

BTEQ -- Enter your DBC/SQL request or BTEQ command:

The ODBC layer in the SQL Assistant takes care of the conversion FLOAT/TIME, and this is why the query runs OK in SQLA, but fails in BTEQ.

HTH.

Cheers.

Carlos.
Enthusiast

Re: I’m wanting to subtract days from date fields

Carlos,
That seems to hv done the trick - thanks a lot.