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
INTERVALso 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
INTERVALto 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