The Daily WTF: Curious Perversions in Information Technology

Disgruntled DBA · 2006-01-13 Reply Admin

Maurits:
Gilbert and Sullivan used leap years as a crucial point in their Pirates of Penzance musical.

Pirate King: You were apprenticed to us!

Frederick: Until my twenty-first year.

Pirate King: No... until your twenty-first birthday - and going by birthdays you are, as yet, only five... and a quarter.

...

Frederick: I've bound myself to serve the pirate captain... until I reach my one-and-twentieth birthday!

Mabel: But you are twenty-one!

Frederick: I've just discovered that I was born in leap year... and that birthday will not be reached by me till 1940.

Given that the play begins when Frederick is twenty-one years old... what year is it?

I would say 1853, since 1900 has no leap day.

lofwyr · 2006-01-13 Reply Admin

One could alos have written it in plain sql (using oracle) ... (thanks to ammoq).

SELECT CustomerId, EmailAddress
FROM Customers
-- get's all Customers, even those on the 29th, if birthday and current year are a leap year
WHERE to_Char(Birthdate, 'MMDD') = to_char(sysdate + 1, 'MMDD')
-- get's all the Customers with birthday on leap year and current year non leap year

OR Decode(to_Char(Birthdate, 'MMDD'), '0229', '0301') = to_char(sysdate + 1, 'MMDD');

l.

2006-01-13 Reply Admin

In Oracle, i might do this:

SELECT CustomerId, EmailAddress
FROM Customers
WHERE TO_CHAR( Birthdate, 'ddd' ) = TO_CHAR( Sysdate, 'ddd' )

Feb 29th would match to March 1 on non leap years.

Maurits · 2006-01-13 Reply Admin

Re: Pirates of Penzance - more discussion (by experts in the field) is available at

http://math.boisestate.edu/gas/pirates/discussion/2.html

Another "gotcha" is that the Major-General's song (I am the very model of a modern major-general) refers to "that infernal nonsense Pinafore" which puts a lower bound on the date...

Jenny Simonds · 2006-01-13 Reply Admin

Alex Papadimoulis:
He showed Steve the above code, unsure of why it didn't return any customers. Steve explained that his query will only find customers who have not been born yet, so he went back to the drawing board ...
SELECT CustomerId, EmailAddress
 FROM Customers
 WHERE DATEPART(day, Birthdate) = DATEPART(day, Today() + 1)
 AND DATEPART(month, Birthdate) = DATEPART(month, Today() + 1)
Steve looked over the code and was impressed...

Regardless of the leap-year problem, won't this code send out the coupon one day and one month before the birthday? And wouldn't this code fail if the customer's birthday is in January?

Anyway, this approach is also the one I thought of. I guess I would solve the problem like this:

Take the Birthdate & change the year part to this year.
Convert today's date & customer's birthday to a julian date (days since 0000-01-01). I assume that any decent SQL will recognize March 1 as the day after Feb. 28 or 29 correctly depending on the year. Why should I have to even think about leap years?
Subtract today's jdate from birthday jdate. If difference is <=1, and this year's birthday coupon hasn't already been sent, then select the record.
Send the coupon.
Update the LastBirthdayCoupon column to the year part of this year's birthday.

SELECT CustomerID, EmailAddress
  FROM Customers
 WHERE TO_DAYS(this-years-birthday, CURDATE()) <= 1
       && LastBirthdayCoupon != YEAR(CURDATE());

Hmmm... so what's the easiest way in MySQL (or some other SQL) to produce this-years-birthday. Would "CONCAT(YEAR(CURDATE(), "-", MONTH(Birthday), "-", DAY(Birthday))" work?

Jenny Simonds · 2006-01-13 Reply Admin

Err, let's try that again:

SELECT CustomerID, EmailAddress
  FROM Customers
 WHERE TO_DAYS(this-years-birthday) - TO_DAYS(CURDATE())) <= 1
       && LastBirthdayCoupon != YEAR(CURDATE());

I'm pretty sure that "CONCAT(YEAR(CURDATE(), "-", MONTH(Birthday), "-", DAY(Birthday))" will produce this-years-birthday.

Maurits · 2006-01-13 Reply Admin

Jenny Simonds:
Alex Papadimoulis:
 WHERE DATEPART(day, Birthdate) = DATEPART(day, Today() + 1)
 AND DATEPART(month, Birthdate) = DATEPART(month, Today() + 1)
Regardless of the leap-year problem, won't this code send out the coupon one day and one month before the birthday? And wouldn't this code fail if the customer's birthday is in January?

No. Watch those parentheses.

vDave420 · 2006-01-14 Reply Admin

kipthegreat:
Anonymous:
(I once read that the septics use less commas than the brits do in their version of english, so I guess communication failures across the pond are inevitable)

I, as an American, who, incedentally, is not familiar with the term "septic," except as relating to a tank full of shit, [...]

My first thought reading that as well.

-dave-

lofwyr · 2006-01-14 Reply Admin

Jenny Simonds:
Take the Birthdate & change the year part to this year.

I'm afraid if you try to convert that date in a a non leap year you'll get an error - hopefully.

l.

olddog · 2006-01-14 Reply Admin

Fearing the worst... That someday soon, I might actually have to implement this exact same project in MySQL, I put together this query. The query focuses on the day and lets the DB handle the leap year calculation.

Turns out the hardest part about finding the 29th on the 27th ( of a non-leap year ), was NOT finding it again on the 28th ( of a leap year ).

SELECT
 CustomerId,
 EmailAddress,
 Birthdate
FROM
 Customers
WHERE
/* Birth-day is tomorrow. */
/* Birth-month is tomorrow. */
/* this naturally finds feb 29 when curr-year is leap year */
(
 DAY( Birthdate) = DAY( DATE_ADD(CURDATE(), INTERVAL 1 DAY))
AND MONTH(Birthdate) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 DAY))
)

/* when curr-year is non leap year */
/* find rouge day at end of month - ie feb 29 */
OR
(
 DAY(DATE_ADD(Birthdate, INTERVAL 1 DAY)) = 1 /* day after birthday is the 1st */
AND DAY(DATE_ADD(CURDATE(), INTERVAL 2 DAY)) = 1 /* day after tomorrow is the 1st */
AND MONTH(Birthdate) = MONTH(CURDATE()) /* curr-month is birth-month */
)

Tested briefly on MySQL 5.0.16-nt replacing CURDATE() with hard-coded dates using DATE('2000-02-27'), etc.

Jenny Simonds · 2006-01-14 Reply Admin

Maurits:
No. Watch those parentheses.

(squinting intently...) Oh yeah, you're right!

lofwyr · 2006-01-14 Reply Admin

Anonymous:
In Oracle, i might do this:

SELECT CustomerId, EmailAddress
FROM Customers
WHERE TO_CHAR( Birthdate, 'ddd' ) = TO_CHAR( Sysdate, 'ddd' )

Feb 29th would match to March 1 on non leap years.

Interesting, but for one, you are missing the condition that it has to look for birthdays on the next day, not the current date. And there is of course the question if you compare birthdays from leap years (and I'm not talking about Feb 29th) with current dates from non leap years. Customers then always get notified a day after their birthdays (for all dates after Feb 29th).

l.

2006-01-15 Reply Admin

Whyever not just:

WHERE DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

Wouldn't that do the job, and get rid of all this Leap-Year-Discussion nonsense?

lofwyr · 2006-01-15 Reply Admin

Anonymous:
Whyever not just:

WHERE DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

Wouldn't that do the job, and get rid of all this Leap-Year-Discussion nonsense?

You ask others about the implication of your solution? Why not test it yourself?

l.

2006-01-15 Reply Admin

lofwyr:
Anonymous:
Whyever not just:

WHERE DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

Wouldn't that do the job, and get rid of all this Leap-Year-Discussion nonsense?

You ask others about the implication of your solution? Why not test it yourself?

I don't have access to an SQL server.

lofwyr · 2006-01-15 Reply Admin

Anonymous:
lofwyr:
Anonymous:
Whyever not just:

WHERE DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

Wouldn't that do the job, and get rid of all this Leap-Year-Discussion nonsense?

You ask others about the implication of your solution? Why not test it yourself?

I don't have access to an SQL server.

And? Use plain old computational logic, or don't you have access to that either?

l.

olddog · 2006-01-15 Reply Admin

Anonymous:

I don't have access to an SQL server.

Me either...nor experience with it. But, I admire someone who is not afraid to stick their neck out and ask why not.

That said, a simple google on DATEDIFF took me right to the MSDN library. So...after a few minutes research, the expression:

DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

simplified to this:

"days tween today and birthday" != "days tween tomorrow and birthday"

Seems likely that would be the case more often than not.

Although...I gotta admit( not knowing much about MSSQL), I may have mis-understood the intent your expression. It was unclear to me what GETDATE()+1 resolves to... is it a day from now, a minute from now, a year from now?

CfP · 2006-01-15 Reply Admin

olddog:

Anonymous:

I don't have access to an SQL server.

Me either...nor experience with it. But, I admire someone who is not afraid to stick their neck out and ask why not.

That said, a simple google on DATEDIFF took me right to the MSDN library. So...after a few minutes research, the expression:

DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

simplified to this:

"days tween today and birthday" != "days tween tomorrow and birthday"

Seems likely that would be the case more often than not.

Although...I gotta admit( not knowing much about MSSQL), I may have mis-understood the intent your expression. It was unclear to me what GETDATE()+1 resolves to... is it a day from now, a minute from now, a year from now?

Even without checking the MSDN documentation, it seems pretty clear to me that the 'y' in the DATEDIFF call is specifying years, not days. And as to GETDATE()+1, clearly the addition of 1 to a date should add days. So the expression:

DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

should amount to:

"years (not days) tween today and birthday" != "years (not days) tween tomorrow and birthday"

which, unless I'm seriously missing something myself, should indeed work for all cases. But of course I'd have to test that before depending on it!

2006-01-16 Reply Admin

In a non-leap year, a 29th February baby will celebrate their birthday depending on how they round.

Round Down: 28th February
Round Up: 1st March
etc....

Haven't we done this before?

2006-01-16 Reply Admin

mlathe:
Anonymous:
Hang on, that last query would work, wouldn't it?

doesn't quite work... but he's right this is a big pain in the butt. you can't just do
... OR ( sysdate = feb 28 and birthday = feb 29)
since in a leap year a feb 29 person would get TWO emails... one on the 28 and one on the 29 (their actual birthday). That means you need to add logic to know if its an actual leap year.

this is starting to look pretty hard to do

not really, especially if you don't care about it working right in the year 2400 ;)

Maurits · 2006-01-16 Reply Admin

Alas,

DATEDIFF('y',GetDate(),Birthday) != DATEDIFF('y',GetDate()+1,Birthday)

does not work.

It will send everyone their coupons on December 31.

DateDiff counts the number of year-boundaries BETWEEN two dates.

So
DateDiff('y', 'Jan 1 2006', 'Dec 31 2007') = 1
DateDiff('y', 'Dec 31 2006', 'Jan 1 2007') = 1

2006-01-16 Reply Admin

I have, in my life, known two people born on a leap-year. They are usually full of excitement in saying odd things like they are only 1/4th the age they appear, and so on, much as technical people are unduly fascinated with the minutia of their machines that surround them. Having also realized that this second round of code works for as often as these people like to celebrate their birthdays, I say they get what they deserve. If your birthday only comes once every four years, so be it. All the more reason to go out and get your due marketing freebies.

2006-01-16 Reply Admin

In Oracle, I would do:

SELECT CustomerId, EmailAddress
FROM Customers
WHERE MOD(MONTHS_BETWEEN(sysdate + 1, birthdate), 12) = 0

Because:

SQL> SELECT MONTHS_BETWEEN(TO_DATE('28-feb-2006', 'dd-mon-yyyy'),
2 TO_DATE('29-feb-2000', 'dd-mon-yyyy')) bday
3 FROM dual;

 BDAY
----------
 72

SQL> SELECT MONTHS_BETWEEN(TO_DATE('01-Mar-2006', 'dd-mon-yyyy'),
2 TO_DATE('29-feb-2000', 'dd-mon-yyyy')) bday
3 FROM dual;

 BDAY
----------
72.0967742

2006-01-16 Reply Admin

Got it.

SELECT FROM Table WHERE ROUND(DATEDIFF(d, '02/29/1980', GETDATE()) % 365.25, 0, 1) = 364

As:

ROUND(DATEDIFF(d, '02/29/1980', '02/27/2005') % 365.25, 0, 1) = 364
ROUND(DATEDIFF(d, '02/29/1980', '02/28/2005') % 365.25, 0, 1) = 365

ROUND(DATEDIFF(d, '02/29/1980', '02/29/2004') % 365.25, 0, 1) = 0
ROUND(DATEDIFF(d, '02/29/1980', '03/01/2005') % 365.25, 0, 1) = 0

(And just to be safe:

ROUND(DATEDIFF(d, '02/27/1980', '02/26/2005') % 365.25, 0, 1) = 364
ROUND(DATEDIFF(d, '02/27/1981', '02/26/2005') % 365.25, 0, 1) = 364
ROUND(DATEDIFF(d, '02/27/1980', '02/27/2005') % 365.25, 0, 1) = 0
)

2006-01-17 Reply Admin

<quote>I thought that people who are born on Feb 29th, usually officially declare their birthday as either February 28th or March 1st, so it still shouldn't be an issue.</quote>

That's a little unfair. Do you randomly "fix" your birthday in one direction or the other?

2006-01-17 Reply Admin

Anonymous:
Anonymous:
I did it a little differently... slightly shorter and doesn't need DATEPART

... WHERE
DATEADD(day, -1, DATEADD(year, DATEDIFF(year, @BirthDate, getdate()), @BirthDate)) = GETDATE()

...and doesn't seem to work for Feb 29th.

Does for me, on Sybase. I tested before I posted. Maybe microsoft broke it?

2006-01-25 Reply Admin

The really simple answer is to not allow birthdays to be set on Feb 29th in the first place.

;)

2006-01-26 Reply Admin

Hey, it's not that complicated:

select
*

from
Customers
where
DATEPART(mm, birthdate - 1) = DATEPART(mm, GetDate())
AND
DATEPART(dd, birthdate - 1) = DATEPART(dd, GetDate())
OR
(DATEPART(mm, birthdate) = 2
AND
DATEPART(dy, GetDate()) = DATEPART(dy, birthdate - 1))

DY stands for day of year...

2008-07-02 Reply Admin

2010-04-19 Reply Admin

Or, using sqlite:

SELECT CustomerId, EmailAddress FROM Customers WHERE Birthdate = date('now','+1 day')

A Happy Happy Birthday

Leave a comment on “A Happy Happy Birthday”