I got this to work on TSM 5.1.6.5:
 
select node_name as "Hostname", filespace_name as
"Filespace", date(backup_end) as "Last" from filespaces
where backup_end<current_timestamp-1 day or backup_end is null order by
"Last"

--

Mark Stapleton


        -----Original Message----- 
        From: Alexander Lazarevich [mailto:alazarev AT ITG.UIUC DOT EDU] 
        Sent: Wed 8/27/2003 2:42 PM 
        To: ADSM-L AT VM.MARIST DOT EDU 
        Cc: 
        Subject: Re: select backup_end - only date?
        
        

        Still doesn't work:
        
        tsm: XXXXXX>select node_name as "Hostname", filespace_name as
        "Filespace", date(backup_end) as "Last completed backup" from filespaces
        where backup_end<current_timestamp-1 day or backup_end is null order by
        backup_end
        
        ANR2964E The reference 'BACKUP_END' is not a column of the SQL result
        table.
        
                 .....................................................V.........
                 rrent_timestamp-1 day or backup_end is null order by backup_end
        
        ANS8001I Return code 3.
        
        
        If I replace ALL references of backup_end with data(backup_end) it 
doesn't
        work either and gives the following error:
        
        ANR2904E Unexpected SQL key word token - 'DATE'.
        
        I go through the script, no matter what backup_end's are replaced with
        date(backup_end), I can't get it to work.
        
        Any other ideas?
        
        Thanks!
        
        Alex
        
        On Wed, 27 Aug 2003, Fred Johanson wrote:
        
        > Try ...date(backup_end) as ...
        >
        >
        > At 02:21 PM 8/27/2003 -0500, you wrote:
        > >I've got a tsm script which works fine, but I only want the date from
        > >backup_end table, not the time. Anyone know how to get the date only 
from
        > >backup_end? The time on it is not needed and adds to much clutter to 
the
        > >email. I can do it in perl, but there must be some way to do it in 
sql,
        > >which I prefer. Statement is below:
        > >
        > >select node_name as "Hostname", filespace_name as "Filespace", 
backup_end
        > >as "Last completed backup" from filespaces where
        > >backup_end<current_timestamp-1 day or backup_end is null order by
        > >backup_end
        > >
        > >Thanks in advance,
        > >
        > >Alex
        > >---                                                               ---
        > >    Alex Lazarevich | Systems Administrator | Imaging Technology 
Group
        > >             Beckman Institute - University of Illinois
        > >        alazarev AT itg.uiuc DOT edu | (217)244-1565 | 
www.itg.uiuc.edu
        > >---                                                               ---
        >