All Forums Database
terankit 77 posts Joined 03/12
08 Nov 2012
Select Tables created in a previous months only

Hi All,
 
I need to get the list of the tables which were created in the last month only.
Ex. If I run this query on 3rd November'12, it should give me the list of the tables created during 1st October to 31st October'12.
Please help.
 
Thanks,
Terankit

ulrich 816 posts Joined 09/09
09 Nov 2012
select (current_date - extract(day from current_Date)) as last_day _of_month,
      Add_months(last_day _of _month + 1,-1) as first_day _of _month;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
09 Nov 2012

Thanks Ulrich.
I have made a query as given below:
SELECT DBNAME,                                            
       TBLNAME,                                               
       CAST(CREATTS AS DATE FORMAT 'YYYY-MM-DD')(CHAR(10))    
       AS CREATE_DATE                                                 
FROM DBC.TABLES                                                       
WHERE DBNAME    = 'DB1'                                    
  AND TBLKIND       = 'T'                                           
  AND CREATE_DATE >= ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, -1)
  AND CREATE_DATE < (DATE - EXTRACT(DAY FROM DATE) + 1)               
  ORDER BY 3,2;   
If I want to make the changes as per your suggestion, then how should this query look like?
 
Thanks                                                 

ulrich 816 posts Joined 09/09
09 Nov 2012

Use help table DBC.TABLES
and fix your column names.
Condition is correct

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

mohan.mscss 31 posts Joined 04/11
09 Nov 2012

Here it is..
SELECT databasename as DBNAME,
tablename as TBLNAME,
CAST(createtimestamp AS DATE FORMAT 'YYYY-MM-DD')(CHAR(10)) as CREATE_DATE                                               
FROM DBC.TABLES                                                        
WHERE DBNAME = 'DB1' AND Tablekind='T' AND
createtimestamp >= ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, -1)
AND
createtimestamp <= (DATE - EXTRACT(DAY FROM DATE))               
ORDER BY 1;
 
 

You must sign in to leave a comment.