Renaming a Column in Teradata

Sometimes it's the little stuff where syntax differences between DMBSs get me.  Today I had to look up how to rename a column.  Easy enough stuff:

ALTER TABLE mydb.foo RENAME OldColumnName TO NewColumnName;

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

Getting Started, my own little “Hello, World!”

I first started this blog as teratara.wordpress.com but somehow got locked out of my account and thus have moved it here.

Most programming lessons start out by teaching you how to get your computer to say “Hello, World!”. This always makes me laugh. I envision a doey-eyed computer-being deep within my maching awakening for the first time, leaning forward with a smile and proclaiming, “Hello, World!”.

Problem solving and language learning has always been fun for me. That said, sometimes my tools for these activities get me to pulling my hair and screwing up my face. The little being deep within my computer then seems to be snickering.

Lately I’ve been working in customer analytics and since last October with Teradata. Teradata is an amazingly power data warehousing solution that is sadly lacking in documentation and online solutions or cookbooks. It’s (almost) ANSI SQL has had me going in the right direction only to hit what seemed a completely illogical and quite nonsensical roadblock. And with no book or good forums to turn to. And so I Google.

Here is where I will post my foibles, syntax conquests, solutions, and tools. As I branch into other DMBSs and visualization tools I will share my code and links to good resources. This will be my online notebook. I hope you’ll contribute.