Monday, October 19, 2009

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 ==
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
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

No comments:

Post a Comment