Not many things start at 1. You’re not 1 until you’ve passed the anniversary of your birth, you never START with 1 apple, you’re always given one or buy one. There’s never one passenger on the bus before the first stop. Luton and Mansfield on Wednesday night even went so far as to END with 0. That’s commitment. The Mansfield keeper must have felt justified in his choice of kit; he looked like he was wearing a full-body condom. Safe as houses. Luton’s very own Pilkington made two fabulous goal line clearances as well. That’s George ‘Crystal’ Pilkington, the centre half – not Kevin ‘determined to be dropped’ Pilkington the goalkeeper. Still – Mike joined me and he seemed to enjoy himself.
Anyway, why do dates start at 1? The First of January seems a reasonable description, but January 1 sounds like it’s done and dusted, so surely it should be January 0 until midnight? Did those Arabs come up with the number meaning “nothing” for, er, no reason? Hmmm… counter argument.
I actually enjoy playing with dates in Teradata. Slightly odd, I’m aware, but that’s just me. I just wish dates started at zero – you’ll see what I mean, I hope.
First – a quick precis of the DATE data type as employed by Teradata.
Teradata dates are stored as an INTEGER with the following layout: CYYMMDD.
C is the ‘century indicator’ – 0 for 1900s, 1 for 2000, 2 for 2100, etc.
YYMMDD are your standard pre-Y2K year, month and day values.
You can perform maths against a DATE value just as if it were an INTEGER but DATE conversions are applied which gets, er, interesting. By this I mean:
DateVal = 1120131 /* Set up DateVal with 31st January 2012 */
If we now add 1 to
DateVal, it has a value of 1120201 (1st February 2012), not 1120132 (32nd January 2012? – obviously an invalid date).
You see what I mean, I’m sure…
The bit I really like is writing derivations from the date that give us what we want. There are functions such as
EXTRACT (YEAR / MONTH / DAY FROM DATE) etc.. These are all described in detail in the Teradata Manuals, but they’re no fun and no challenge.
To get the year, I use the following:
1900 + (DATE / 10000)
DATE is an INTEGER, 10000 is an INTEGER, so you get an INTEGER back.
For Day, it’s simpler:
DATE MOD 100
(NB: MOD gives you the remainder after dividing by the value supplied).
Month is a little trickier:
(DATE / 100) MOD 100
So far so straightforward. Now let’s say you want the last day of last month (a frequent requirement in Financial institutions):
We need to take off the number of days we are into the existing month:
DATE - (DATE MOD 100)
This will always give you the last day of last month.
So to a problem I’ve had to solve recently. I confess to enlisting Jon over a coffee to discuss it as we tend to clear each other’s minds when ‘there must be an easier way’ keeps rattling through my head.
For any given Maturity Date (in a DATE column named
Maturity), we want to derive the first day of the Quarter in which that date falls.
Quarters are simply defined as Jan to Mar, Apr to Jun, Jul to Sep, Oct to Dec.
Getting the Year is easy – as shown above.
The Day will always be 1 as we want the first Day of the Quarter.
The tricky bit is getting the first month of the quarter we’re in. We need 1 for 1 ,2 ,3; 4 for 4, 5, 6; 7 for 7, 8, 9; 10 for 10, 11, 12.
We’ll assume that
MatMonth is derived, as above, by:
((Maturity / 100) MOD 100)
(((INT((MatMonth - 1) / 3) + 1) * 3) - 2)
MatMonth - ( ( MatMonth - 1) MOD 3)
They both work and will be similarly efficient. I tend to prefer Jon’s method as we’re only deriving the month once. Jon apparently prefers mine. It’s just mutual appreciation, I think…
But in both of them you have to juggle the month up and/or down by 1. Grrr. If dates started at 0, this wouldn’t be necessary. It’s just messy.
Finally, here’s the full script, with the items broken down:
(Maturity / 10000) AS MatYear /* Maturity Year */
,((Maturity / 100) MOD 100) AS MatMonth /* Maturity Month */
,(Maturity MOD 100) AS MatDay /* Maturity Day */
,MatMonth - ((MatMonth - 1) MOD 3) AS MatQStart /* Start Month of Maturity Quarter */
, (MatYear * 10000) /* Shuffle year left 4 places */
+ (MatQStart * 100) /* Shuffle our Quarter Start Month up 2 places */
+ 1 /* Just Day 0 - oops - I mean 1, of course */
AS Calc_QStart /* Our Calculated Quarter Start as an INTEGER */
,CAST (Calc_QStart AS DATE) /* Belt and braces to ensure we have a DATE */
Obviously you could put this all in one output column by combining the factors in one statement.
I’m going to leave it there for now. If you ‘d like an explanation of how these work, I’ll post as a comment. For info, both Jon and I resort to the demon e(X)cel* to build these things step-by-step.
* Yes, that is a Xanth reference. More on that another time.