| Groups | Blog | Home |
sql server programming : Bday dates revisited
This code takes the date of birth, uses the current year an calculates the difference between now and the birthday for this year: declare @dob datetime set @dob = '1960-04-20' select datediff (dd, getdate(), str (year (getdate()), 4) + right (convert (char (8), @dob, 112), 4)) -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. [quoted text, click to view] "Mike_B" <nospam@yahoo.com> wrote in message Hi,news:ervuHLZYGHA.3848@TK2MSFTNGP05.phx.gbl... I'm trying to create a query that will tell me when someone's birth day is approaching from my employee table. I tried Case When Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but not for the month). If I And or Or with Datepart(mm,b_day) - Datepart(mm,b_day) < 0 doesn't work either. I'm sure there's a simple way out of this. Can someone help pls. TIA Mike
Hey,
Just be aware that leap year birthdays will break this code. Run the following and see (note that I used a variable to change the comparison date): DECLARE @CompDate datetime --SET @CompDate = GETDATE() SET @CompDate = '20060225' declare @t table ( Employee varchar (20) primary key , DOB datetime not null ) insert @t (DOB, Employee) values ('1975-04-19', 'Bob') insert @t (DOB, Employee) values ('1934-03-03', 'Mary') insert @t (DOB, Employee) values ('1956-03-26', 'David') insert @t (DOB, Employee) values ('1958-04-06', 'Sonia') insert @t (DOB, Employee) values ('2000-02-29', 'Leap') select Employee from @t where datediff (dd, @CompDate, str (year (@CompDate), 4) + right (convert (char (8), DOB, 112), 4)) between 0 and 4 There must be a better way, but here's my quick patch for Tom's code: select Employee from @t where datediff (dd, @CompDate, str (year (@CompDate), 4) + REPLACE(right (convert (char (8), DOB, 112), 4), '0229', '0228')) between 0 and 4 HTH, Stu
Try:
declare @t table ( Employee varchar (20) primary key , DOB datetime not null ) insert @t (DOB, Employee) values ('1975-04-19', 'Bob') insert @t (DOB, Employee) values ('1934-03-03', 'Mary') insert @t (DOB, Employee) values ('1956-03-26', 'David') insert @t (DOB, Employee) values ('1958-04-06', 'Sonia') select Employee from @t where datediff (dd, getdate(), str (year (getdate()), 4) + right (convert (char (8), DOB, 112), 4)) between 0 and 4 Part of the problem is the filtering. Some of these people have birthdays that have already passed. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. [quoted text, click to view] "Mike_B" <nospam@yahoo.com> wrote in message Thank you for your quick response, Tom.news:uj%23yBjZYGHA.4916@TK2MSFTNGP04.phx.gbl... I tried the following query: Select Case When Datediff (dd, Getdate(), str (year (Getdate()), 4) + Right (convert (char (8), b_day, 112), 4)) < 4 then dbo.employees ..employee_name Else '' End As TEST From dbo.employees And as a result I get: 1975-04-19 Bob 1934-03-03 Mary 1956-03-26 David 1958-04-06 Sonia I expected Bob to be the only results since he has an upcoming bday which is within 4 days. Mike "Tom Moreau" <tom@dont.spam.me.cips.ca> escribió en el mensaje news:u%23wOOSZYGHA.3868@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] > The problem might be with you storing "birthday" and not "date of birth". > This code takes the date of birth, uses the current year an calculates the > difference between now and the birthday for this year: > > declare > @dob datetime > > set @dob = '1960-04-20' > > select > datediff (dd, getdate(), str (year (getdate()), 4) + > right (convert (char (8), @dob, 112), 4)) > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Toronto, ON Canada > . > "Mike_B" <nospam@yahoo.com> wrote in message > news:ervuHLZYGHA.3848@TK2MSFTNGP05.phx.gbl... > Hi, > > I'm trying to create a query that will tell me when someone's birth day is > approaching from my employee table. I tried Case When > Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk > > Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but > not > for the month). If I And or Or with Datepart(mm,b_day) - > Datepart(mm,b_day) > < 0 doesn't work either. > > I'm sure there's a simple way out of this. Can someone help pls. > > TIA > > Mike > >
Hi,
I'm trying to create a query that will tell me when someone's birth day is approaching from my employee table. I tried Case When Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but not for the month). If I And or Or with Datepart(mm,b_day) - Datepart(mm,b_day) < 0 doesn't work either. I'm sure there's a simple way out of this. Can someone help pls. TIA Mike
Thank you for your quick response, Tom.
I tried the following query: Select Case When Datediff (dd, Getdate(), str (year (Getdate()), 4) + Right (convert (char (8), b_day, 112), 4)) < 4 then dbo.employees ..employee_name Else '' End As TEST From dbo.employees And as a result I get: 1975-04-19 Bob 1934-03-03 Mary 1956-03-26 David 1958-04-06 Sonia I expected Bob to be the only results since he has an upcoming bday which is within 4 days. Mike "Tom Moreau" <tom@dont.spam.me.cips.ca> escribió en el mensaje news:u%23wOOSZYGHA.3868@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] > The problem might be with you storing "birthday" and not "date of birth". > This code takes the date of birth, uses the current year an calculates the > difference between now and the birthday for this year: > > declare > @dob datetime > > set @dob = '1960-04-20' > > select > datediff (dd, getdate(), str (year (getdate()), 4) + > right (convert (char (8), @dob, 112), 4)) > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Toronto, ON Canada > . > "Mike_B" <nospam@yahoo.com> wrote in message > news:ervuHLZYGHA.3848@TK2MSFTNGP05.phx.gbl... > Hi, > > I'm trying to create a query that will tell me when someone's birth day is > approaching from my employee table. I tried Case When > Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk > > Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but > not > for the month). If I And or Or with Datepart(mm,b_day) - > Datepart(mm,b_day) > < 0 doesn't work either. > > I'm sure there's a simple way out of this. Can someone help pls. > > TIA > > Mike > >
That's it. Thank you much.
Mike. "Tom Moreau" <tom@dont.spam.me.cips.ca> escribió en el mensaje news:O$0JFtZYGHA.128@TK2MSFTNGP05.phx.gbl... [quoted text, click to view] > Try: > > declare @t table > ( > Employee varchar (20) primary key > , DOB datetime not null > ) > > insert @t (DOB, Employee) values ('1975-04-19', 'Bob') > insert @t (DOB, Employee) values ('1934-03-03', 'Mary') > insert @t (DOB, Employee) values ('1956-03-26', 'David') > insert @t (DOB, Employee) values ('1958-04-06', 'Sonia') > > select > Employee > from > @t > where > datediff (dd, getdate(), str (year (getdate()), 4) + > right (convert (char (8), DOB, 112), 4)) between 0 and 4 > > Part of the problem is the filtering. Some of these people have birthdays > that have already passed. > > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Toronto, ON Canada > . > "Mike_B" <nospam@yahoo.com> wrote in message > news:uj%23yBjZYGHA.4916@TK2MSFTNGP04.phx.gbl... > Thank you for your quick response, Tom. > > I tried the following query: > > > > Select > > Case When Datediff (dd, Getdate(), str (year (Getdate()), 4) + > > Right (convert (char (8), b_day, 112), 4)) < 4 then dbo.employees > .employee_name Else '' End As TEST > > From dbo.employees > > > > > > And as a result I get: > > > > 1975-04-19 Bob > > 1934-03-03 Mary > > 1956-03-26 David > > 1958-04-06 Sonia > > > > I expected Bob to be the only results since he has an upcoming bday which > is > within 4 days. > > Mike > > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> escribió en el mensaje > news:u%23wOOSZYGHA.3868@TK2MSFTNGP04.phx.gbl... >> The problem might be with you storing "birthday" and not "date of birth". >> This code takes the date of birth, uses the current year an calculates >> the >> difference between now and the birthday for this year: >> >> declare >> @dob datetime >> >> set @dob = '1960-04-20' >> >> select >> datediff (dd, getdate(), str (year (getdate()), 4) + >> right (convert (char (8), @dob, 112), 4)) >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Toronto, ON Canada >> . >> "Mike_B" <nospam@yahoo.com> wrote in message >> news:ervuHLZYGHA.3848@TK2MSFTNGP05.phx.gbl... >> Hi, >> >> I'm trying to create a query that will tell me when someone's birth day >> is >> approaching from my employee table. I tried Case When >> Datediff(dd,b_day,getdate()) < 4 Then 'Remind' Else '' End As B_dayCk >> >> Datepart(dd,b_day) - Datepart(dd,b_day) < 4 (This checks for the day but >> not >> for the month). If I And or Or with Datepart(mm,b_day) - >> Datepart(mm,b_day) >> < 0 doesn't work either. >> >> I'm sure there's a simple way out of this. Can someone help pls. >> >> TIA >> >> Mike >> >> > >
You already have a Calendar table, right? Of course you do; it is one
of the first things you put in a schema. So add a column for "(n) business days in the future". This kind of column is used to get due dates for business purposes, where n = {30, 60, 90, 120}; you just want to have (n=4) from your example.
Joe,
I prefer to do it slightly differently: I have a business_day_number column in my Calendar table. That done, 17 business days after 20060420 is selected as select later.business_day from Calendar c1 join Calendar later on later.business_day_number = c1.business_day_number + 17 where c1.business_day = '20060420' For a non business day, such as Saturday, business_day is set to next work day, otherwise business_day coincides with the date. Advantages: I don't need to add another column for each and every value of n, and I have less maintenance when all of a sudden they say January 2 is a work day - I need to modify only 2 columns, business_day and business_day_number. Disadvantage: lookups are just a tad slower. Makes sence?
[quoted text, click to view]
>> Makes sence? << Yes! Julianized business days are a good idea. I am not sure if ther lookups and math are jslower. In business use, the "magic numbers" are {30, 60, 90, 120} days from an event. i can probably get that many rows into main storage for a join.
Good point Stu.
So you're replacing the leap year to a non leap year? "Stu" <stuart.ainsworth@gmail.com> escribió en el mensaje news:1145230290.066560.260910@j33g2000cwa.googlegroups.com... [quoted text, click to view] > Hey, > > Just be aware that leap year birthdays will break this code. Run the > following and see (note that I used a variable to change the comparison > date): > > DECLARE @CompDate datetime > --SET @CompDate = GETDATE() > SET @CompDate = '20060225' > > declare @t table > ( > Employee varchar (20) primary key > , DOB datetime not null > ) > > insert @t (DOB, Employee) values ('1975-04-19', 'Bob') > insert @t (DOB, Employee) values ('1934-03-03', 'Mary') > insert @t (DOB, Employee) values ('1956-03-26', 'David') > insert @t (DOB, Employee) values ('1958-04-06', 'Sonia') > insert @t (DOB, Employee) values ('2000-02-29', 'Leap') > > > select > Employee > from > @t > where > datediff (dd, @CompDate, str (year (@CompDate), 4) + > right (convert (char (8), DOB, 112), 4)) between 0 and 4 > > There must be a better way, but here's my quick patch for Tom's code: > > > select > Employee > from > @t > where > datediff (dd, @CompDate, str (year (@CompDate), 4) + > REPLACE(right (convert (char (8), DOB, 112), 4), '0229', '0228')) > between 0 and 4 > > HTH, > Stu >
Yeah, Tom's code would create a comparison value of '20060229', which
is a non-existant date; mine simply ignores all dates of '0229', making them '0228' instead. Note that even in a leap year, the comparison will still go against '0228' rather than the correct date. However, the idea of a calendar table is probably the best way to go; I especially like Alexander's join concept. Stu
Would this be an option?
USE northwind select firstname, lastname, birthdate, dateadd(year,(datediff(year,birthdate, getdate())),birthdate) as birthday from employees Where (select dateadd(year,(datediff(year,birthdate, getdate())),birthdate)) between getdate() and getdate()+4 Ratcliff
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |