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.
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;
I have made a query as given below:
CAST(CREATTS AS DATE FORMAT 'YYYY-MM-DD')(CHAR(10))
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?
Here it is..
SELECT databasename as DBNAME,
tablename as TBLNAME,
CAST(createtimestamp AS DATE FORMAT 'YYYY-MM-DD')(CHAR(10)) as CREATE_DATE
WHERE DBNAME = 'DB1' AND Tablekind='T' AND
createtimestamp >= ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, -1)
createtimestamp <= (DATE - EXTRACT(DAY FROM DATE))
ORDER BY 1;