[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