Teradata Date Conversion - Getting the First day of the Month

I have a table containing start dates and I want to identify all the records for a given month by their month. This will allow me to identify sets of users by their signup month.

Thanks to a tip from Craig F. I can do this by subtracting from the date the day of the month minus 1. This will ensure that regardless of the date I choose, all my dates are returned as the first of the month.

I created a table (foo) with one column (startdt type DATE) and three records:

select * from foo

== Results ==
startdt
12/31/2009  12:00:00 AM
8/1/2009    12:00:00 AM
7/15/2009  12:00:00 AM

Now I want to get the number of days to subtract from the day in the date. To do this, I use Teradata’s EXTRACT function and get the day of the date:

EXTRACT(Day from StartDt) as DayOfDate 

and I get the number day value : 31, 1, 15

EXTRACT(Month from StartDt) returns the month values 12, 8, 7

EXTRACT(Year from StartDt) returns 2009

Now that I have the numeric day of the month I simply subtract one from it:

SELECT startdt
, startdt - INTERVAL '1' DAY as Start_Minus_1_Day
, EXTRACT(Day from StartDt) -1 as DaySubAmount
, startdt - (EXTRACT(DAY from StartDt) -1) as FirstofMonth
FROM database.foo

You'll notice another function you can do with dates is add or subtract an INTERVAL. There is a lot you can do with INTERVAL so I'll limit my comments here to how I used it.

Above I subtracted 1 day from the date using - INTERVAL '1' DAY. Day could be replaced with Month, Year, Hour, Minute, Second and transformations (like Day to Second). You can also Add an INTERVAL to a date using the same syntax.

My final result set was:

startddt Start_Minus_1_Day DaySubAmount FirstofMonth
8/1/2009 0:00 7/31/2009 0:00  0  8/1/2009 0:00
7/15/2009 0:00 7/14/2009 0:00  14  7/1/2009 0:00
12/31/2009 0:00 12/30/2009 0:00  30  12/1/2009 0:00