[thelist] SQL: Birthday report (solved)

[Seth Bienek]

As it turns out, SQL Server supports the use of '>' for equality
checking (of course), but it does not return 1 or 0, but rather TRUE or
FALSE, which obviously can't be subtracted from anything. :)

So it was back to the drawing board.  My original plan was to use the
same concept, but to execute it using a CASE statement, but while doing
some more research, I stumbled across a very intriguing and, dare I say,
slick approach to finding birthdays within a date range, by David
Penton:

"This sample simply creates comparable numeric values by multiplying all
months by 100 and then adding the day value."
http://aspsmith.com/DesktopDefault.aspx?tabindex=2&tabid=42&paged_articl
e_id=34

A few tweaks and I had this baby doing everything I needed!

I wanted to share it with you all because it elegantly solves what I
believe to be a common challenge for web developers, and also because
there are LOTS of examples for accomplishing this task in SQL that are
out-right incorrect, and this one works.

Thank you Rudy, Darren, and Joshua, for your help and pointers!

Peace Out,

Seth

> -----Original Message-----
> I'm given a task of writing a report to display clients whose
> birthday is within a provided date range.  Sounds simple,
> right?  It ain't.
> ...
> I found a very good article on doing this in MySQL by evolt's
> very own Rudy Limeback:
>
<http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid1
3_cid458485_tax285649,00.html>
>
> The major problem with this approach being that I am using SQL Server
> and the constructs he provides don't work with SQL server,
> specifically this statement:
>
>- ( RIGHT(DATE_ADD(CURRENT_DATE, INTERVAL 14 DAYS),5)
>           < RIGHT(birthdate,5)
>
> Can someone (rudy maybe? :) explain what this does?

it evaluates to 1 or 0          < RIGHT(birthdate,5)