Select Tables created in a previous months only

Database
Enthusiast

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

4 REPLIES
Senior Supporter

Re: Select Tables created in a previous months only

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;

Enthusiast

Re: Select Tables created in a previous months only

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                                                 

Senior Supporter

Re: Select Tables created in a previous months only

Use help table DBC.TABLES

and fix your column names.

Condition is correct

Enthusiast

Re: Select Tables created in a previous months only

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;