This works on 6.2.3.1: select volume_name,stgpool_name,access,pct_utilized,est_capacity_mb, cast(day(current_timestamp-last_write_date) as decimal) as "Days", status,access,last_write_date,substr(char(last_write_date),1,19) as "Last Write" from volumes where status='FILLING' and access!='UNAVAILABLE' and access='OFFSITE' and 60 < cast(day(current_timestamp-last_write_date) as decimal)
- Margaret -----Original Message----- From: ADSM: Dist Stor Manager [mailto:ADSM-L@VM.MARIST.EDU] On Behalf Of Huebner,Andy,FORT WORTH,IT Sent: Wednesday, March 07, 2012 1:40 PM To: ADSM-L@VM.MARIST.EDU Subject: [ADSM-L] TSM 6.2 SQL statement We run this and other SQL statements that return dates in the form of days. This SQL query works on 5.4, but does not work on 6.2. I am hoping one of the more SQL savvy admins can quickly help me with this so I can fix the rest of the queries we use. The lines that don't work and I do not know how to translate are: (current_timestamp - last_write_date)days as "Days" and cast((current_timestamp - last_write_date)days as decimal(6,0)) \> 60 \ select volume_name, \ stgpool_name, PCT_UTILIZED, EST_CAPACITY_MB, \ (current_timestamp - last_write_date)days as "Days", \ status, access, last_write_date, \ cast ((last_write_date) as date) as "Last Write" \ from volumes \ where status = 'FILLING' \ and access != 'UNAVAILABLE' and access = 'OFFSITE' \ and cast((current_timestamp - last_write_date)days as decimal(6,0)) \> 60 \ order by last_write_date Thanks, Andy Huebner This e-mail (including any attachments) is confidential and may be legally privileged. If you are not an intended recipient or an authorized representative of an intended recipient, you are prohibited from using, copying or distributing the information in this e-mail or its attachments. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete all copies of this message and any attachments. Thank you.
