1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

Simple query about timestamps

Discussion in 'Scripting' started by etchingsj, Jul 30, 2012.

  1. etchingsj

    etchingsj Member

    Joined:
    Feb 5, 2008
    Messages:
    243
    Likes Received:
    7
    Occupation:
    Sustaining Engineer
    Location:
    Lost Wages NV
    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
     
  2.  
  3. rmazzon

    rmazzon Senior Member

    Joined:
    Dec 6, 2004
    Messages:
    205
    Likes Received:
    17
    Occupation:
    TSM Managed Services Lead
    Location:
    NC
    yes, current_date
     
  4. etchingsj

    etchingsj Member

    Joined:
    Feb 5, 2008
    Messages:
    243
    Likes Received:
    7
    Occupation:
    Sustaining Engineer
    Location:
    Lost Wages NV
    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
     
    Last edited: Jul 30, 2012
  5. rmazzon

    rmazzon Senior Member

    Joined:
    Dec 6, 2004
    Messages:
    205
    Likes Received:
    17
    Occupation:
    TSM Managed Services Lead
    Location:
    NC
    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
     
  6. etchingsj

    etchingsj Member

    Joined:
    Feb 5, 2008
    Messages:
    243
    Likes Received:
    7
    Occupation:
    Sustaining Engineer
    Location:
    Lost Wages NV
    So how should the completed syntax look?

    Thanks!
     
  7. rmazzon

    rmazzon Senior Member

    Joined:
    Dec 6, 2004
    Messages:
    205
    Likes Received:
    17
    Occupation:
    TSM Managed Services Lead
    Location:
    NC
    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
     
  8. etchingsj

    etchingsj Member

    Joined:
    Feb 5, 2008
    Messages:
    243
    Likes Received:
    7
    Occupation:
    Sustaining Engineer
    Location:
    Lost Wages NV
    Thanks! Damn Emoticons....
     
    Last edited: Jul 30, 2012

Share This Page