To find previous month first date in Teradata

The following query will help you to find the first date of the previous month.

Syntax:
SELECT ADD_MONTHS(input_date - EXTRACT(DAY FROM input_date)+1, -1)


How it works ?

First we extract the day from the date and subtracting from the date itself.
So that we are getting the last date of the last month.

input_date - EXTRACT(DAY FROM input_date) ---> last month's end date.

After that we add '1' to that, so the first date of the present month will be the result.

Please note that whenever we compute the first and last date(s) of a month, it is better to proceed from the start date of the month. (Because, the last date will vary for months i.e. Feb-28, Oct- 31, Nov - 30)

Now we have got the start date of the current month, so it will be easy to compute the start date of previous month by ADD_MONTHS(date,-1) functionality.


Example:
Consider, date_1 table is having data like below
input_date
12/30/2012
02/28/2010
01/12/2011
07/12/2012


SELECT 
input_date,
ADD_MONTHS(input_date - EXTRACT(DAY FROM input_date)+1, -1) AS prev_first_date
FROM date_1


Output:
input_dateprev_first_date
12/30/201211/01/2012
02/28/201001/01/2010
01/12/201112/01/2010
07/12/201206/01/2012