Results 1 to 7 of 7
  1. #1
    Member etchingsj's Avatar
    Join Date
    Feb 2008
    Location
    Lost Wages NV
    Posts
    243
    Thanks
    1
    Thanked 6 Times in 6 Posts

    Question Simple query about timestamps

    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() 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. #2
    Senior Member
    Join Date
    Dec 2004
    Location
    NC
    Posts
    200
    Thanks
    0
    Thanked 12 Times in 11 Posts

    Default

    yes, current_date
    "If we knew what it was we were doing, it would not be called research, would it?" -- Albert Einstein

  3. #3
    Member etchingsj's Avatar
    Join Date
    Feb 2008
    Location
    Lost Wages NV
    Posts
    243
    Thanks
    1
    Thanked 6 Times in 6 Posts

    Default

    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() 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 by etchingsj; 07-30-2012 at 12:16 PM. Reason: Had the same operator twice???

  4. #4
    Senior Member
    Join Date
    Dec 2004
    Location
    NC
    Posts
    200
    Thanks
    0
    Thanked 12 Times in 11 Posts

    Default

    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
    "If we knew what it was we were doing, it would not be called research, would it?" -- Albert Einstein

  5. #5
    Member etchingsj's Avatar
    Join Date
    Feb 2008
    Location
    Lost Wages NV
    Posts
    243
    Thanks
    1
    Thanked 6 Times in 6 Posts

    Default

    Quote Originally Posted by rmazzon View Post
    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!

  6. #6
    Senior Member
    Join Date
    Dec 2004
    Location
    NC
    Posts
    200
    Thanks
    0
    Thanked 12 Times in 11 Posts

    Default

    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
    "If we knew what it was we were doing, it would not be called research, would it?" -- Albert Einstein

  7. #7
    Member etchingsj's Avatar
    Join Date
    Feb 2008
    Location
    Lost Wages NV
    Posts
    243
    Thanks
    1
    Thanked 6 Times in 6 Posts

    Default

    Quote Originally Posted by rmazzon View Post
    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....
    Last edited by etchingsj; 07-30-2012 at 05:42 PM.

Similar Threads

  1. A Very Simple Question ....??????? KB/S!!!
    By fanna in forum Backup / Archive Discussion
    Replies: 4
    Last Post: 10-22-2008, 08:24 AM
  2. Very simple SCHEDULE !!!!
    By fanna in forum TDP/Application Layer Backup
    Replies: 14
    Last Post: 03-20-2008, 04:14 AM
  3. Simple SQL query into single-line output?
    By kzw2zx in forum Scripting
    Replies: 10
    Last Post: 02-13-2008, 03:21 PM
  4. simple macro q
    By cabledguy2006 in forum Scripting
    Replies: 4
    Last Post: 09-02-2007, 09:57 PM
  5. Please help..Simple question
    By tsmnewbie in forum TSM Reporting & Monitoring
    Replies: 3
    Last Post: 06-23-2004, 12:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •