Simple query about timestamps

[etchingsj]

To meet an SLA I have a script for a client that is set up as follows.
Is there a way to specify 'TODAY' as opposed to the date format '2012-07-30'?
And yes I tried today and it did not work.

Thanks!


select cast((server_name) as char(10)) AS TSM_SERVER, cast((node_name) as char(20)) AS NODE_NAME,cast((schedule_name)as char(15)) AS Sched_Name,cast((status) as char(8)) AS STATUS, count(*) as "3_DAY_COUNT" from EVENTS, status where (scheduled_start >= '2012-07-30')and (scheduled_start >=current_timestamp - 3 days) and (status='Failed' or status='Missed') and node_name is not NULL group by server_name, node_name,schedule_name, status having count(*)>=2 order by status, "3_DAY_COUNT" desc

 

[rmazzon]

yes, current_date

 

[etchingsj]

Gave that a shot in a couple different ways.

ANR2916E The SQL data types TIMESTAMP and DATE are incompatible for operator '>='.

|
..............................V................................
status where (scheduled_start >=current_date)and (scheduled_sta

ANS8001I Return code 3.
ANR2948E The value 'current_date' can not be assigned or converted to SQL data type TIMESTAMP.

|
........................V......................................
ere (scheduled_start >= 'current_date')and (scheduled_start >=c

ANS8001I Return code 26.

tsm: TSMSERVER>select cast((server_name) as char(10)) AS TSM_SERVER, cast((node_name) as char(20)) AS NODE_NAME,cast((sch
edule_name)as char(15)) AS Sched_Name,cast((status) as char(8)) AS STATUS, count(*) as "3_DAY_COUNT" from EVENTS, status
where (scheduled_start >=current_timestamp - 3 days) and (status='Failed' or s
tatus='Missed') and node_name is not NULL group by server_name, node_name,schedule_name, status having count(*)>=2 order
by status, "3_DAY_COUNT" desc
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

Seemed to get the correct result with this query using current_timestamp and current_timestamp as current_date in both fields did not work either.

Thanks

 

[rmazzon]

You wcan't just compare a timestamp to a date, you need to use a function to convert the timestamp.

date(scheduled_start)=current_date

 

[etchingsj]

You wcan't just compare a timestamp to a date, you need to use a function to convert the timestamp.

date(scheduled_start)=current_date

So how should the completed syntax look?

Thanks!

 

[rmazzon]

Like this. Note I had to guess on the char() for status since I see an emoticon. I also used the "in" function near the end.

select cast((server_name) as char(10)) AS TSM_SERVER, \
cast((node_name) as char(20)) AS NODE_NAME, \
cast((schedule_name)as char(15)) AS Sched_Name, \
cast((status) as char(10)) AS STATUS, count(*) as "3_DAY_COUNT" \
from EVENTS, status where (date(scheduled_start)>=current_date - 3 days) \
and status in ('Failed','Missed') and node_name is not NULL \
group by server_name, node_name,schedule_name, status having count(*)>=2 \
order by status, "3_DAY_COUNT" desc

 

[etchingsj]

Like this. Note I had to guess on the char() for status since I see an emoticon. I also used the "in" function near the end.

Thanks! Damn Emoticons....