[thelist] SQL: Birthday report

darren darren at web-bitch.co.uk
Fri Oct 18 10:29:01 CDT 2002


On Friday, October 18, 2002 at 15:05, Seth Bienek wrote:


SB> The major problem with this approach being that I am using SQL Server
SB> and the constructs he provides don't work with SQL server, specifically
SB> this statement:

SB> - ( RIGHT(DATE_ADD(CURRENT_DATE, INTERVAL 14 DAYS),5)
SB>            < RIGHT(birthdate,5)

if i understand correctly it's taking the rightmost 5 characters from
the date in 14 days time (mm-yy) and seeing if it's less than the
rightmost 5 characters of the birthdate (mm-yy).

to do this is t-sql:

   right(convert(varchar(10), DateAdd(dd, 14, GetDate()), 120), 5)
      < right(convert(varchar(10), birthdate, 120), 5)

the convert takes the datetime and formats it as a string in the format
yyyy-mm-dd, so you can do a right() on it to get the mm-dd.  this matches
the format used in the article.

so the whole thing from the article becomes (if i have all the brackets
correct!  this is untested):

   select
      lastname, birthdate
   from
      yourtable
   where ( YEAR(DateAdd(dd, 14, GetDate()))
            - YEAR(birthdate))
         - ( RIGHT(convert(varchar(10), DateAdd(dd, 14, GetDate()), 120), 5)
            < RIGHT(convert(varchar(10), birthdate, 120), 5) )
         > ( YEAR(GetDate())
            - YEAR(birthdate) )
         - ( RIGHT(convert(varchar(10), GetDate(), 120), 5)
            < RIGHT(convert(varchar(10), birthdate, 120), 5 )


hth,

darren.




More information about the thelist mailing list