date manipulation question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

date manipulation question

Author

Topic

Kervin
Starting Member

12 Posts

Posted - 2003-06-22 : 05:30:00

OS : Windows 2000 5.00.2195 Service Pack 3
SQL : SQL Server Standard Edition 8.00.194(RTM)

Hi.
I am trying to create an sql select statement that gives the records of a table whose date range is 5 days earlier than todays date, dependant on a table field called DOB... sounds easy untill you add that the dates stored in field DOB are birthdays and thus the year will not be this year but the year that person was born.

ie.

The date in the table field DOB is - 06/22/1960

today is - 06/17/2003

The date range I would like returned is 06/17/2003 to 06/22/2003

I also have to take in to account that 5 days earlier may mean that I am in the previous month or in the previous year.

I have tried all sorts of combinations using DATEPART, DATEADD, GETDATE() to try to put the date together in the select with no success. I want to try to do this in the select so that I dont have to go through the entire client record set outside of sql (I am doing this for use in ASP).

As a further explination of what I am trying to do... I have created a client management application. The client's Date Of Birth is stored in the field DOB. I want to have the application give the user a reminder of an upcomming clients birthday 5 days prior to the event.

Thank you for your time.

Kervin.

kervin_findlay@hotmail.com

Edited by - Kervin on 06/22/2003 05:50:36

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-22 : 07:03:36

You may get the list of upcoming birthdays by this:

select dob from tdob where
cast(dob as varchar(6))=cast(getdate()+0 as varchar(6)) or
cast(dob as varchar(6))=cast(getdate()+1 as varchar(6)) or
cast(dob as varchar(6))=cast(getdate()+2 as varchar(6)) or
cast(dob as varchar(6))=cast(getdate()+3 as varchar(6)) or
cast(dob as varchar(6))=cast(getdate()+4 as varchar(6))

Hope this help

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-22 : 07:54:43

-- Got it.

Sam

Edited by - SamC on 06/22/2003 07:58:00

Kervin
Starting Member

12 Posts

Posted - 2003-06-22 : 08:49:19

:-o Wow!

I don't even know what to say to that. This was the first time in 12 years of working with computers that I posted something in a forum... Not only did your answer work perfectly, it was not at all what I expected ( I was sure you were going to come back with some use of Datepart/Dateadd or something ), and even after reading the answer over and over again I still don't understand how or why it works. I have never seen or heard of the cast command before.

Needless to say I will be heading down to my local Chapters Bookstore to do some reading on this fine Sunday morning!

Thank you.

Thankx

Kervin.
kervin_findlay@hotmail.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-22 : 09:03:20

You CAN use DateDiff, DateAdd, and GetDate for this:

SELECT DOB FROM myTable
WHERE DOB BETWEEN DateAdd(d, DateDiff(d, 0, GetDate()), -5) AND GetDate()

Kervin
Starting Member

12 Posts

Posted - 2003-06-22 : 09:23:38

Ok... That one I understood ( and was more along the lines of what I origionaly expected

). Thank you... I did try to find more information on how and why the previous answer using the cast command worked... but sometimes the guys who write for MSDN don't write in english ( or at least not the kind of english my simple mind is able to understand ).

Thankx

Kervin.
kervin_findlay@hotmail.com

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-22 : 10:20:21

I had originally posted something like Rob's solution. But I didn't get the connotation that the you want all records for all years that are within 5 days of today's date.

Rob's solution returns only this year's records. (Probably records for persons too young to use the internet).

Gotta check for 5 days of the day of the year, ignoring the year.

Sam

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-22 : 10:25:34

Oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooops.

I am retard, hear me drool.

Kervin
Starting Member

12 Posts

Posted - 2003-06-22 : 10:28:23

I thought it I had done something wrong... when I was not able to get
Rob's solution working correctly after further testing. You guys sure know your stuff.

Thankx

Kervin.
kervin_findlay@hotmail.com

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-22 : 12:57:01

The only problem with stoad's solution is that it ignores Feb 29th except when today is in a leap-year. This should give you the birthdays for the next 6 days including today.


WHERE
  DATEDIFF(d, GETDATE(), DATEADD(yy, (DATEDIFF(yy, dob, GETDATE()) + CASE WHEN
  DATEADD(yy, DATEDIFF(yy, dob, GETDATE()), dob) >= GETDATE() THEN 0 ELSE 1
  END), dob)) < 6

Funnily enough, The Guru's Guide to Transact-SQL has a bit about this, but the answer given (p.26) is wrong. Sadly, so is the erratum at http://www.khen.com/books/tsqlgurusguide.htm

Edited by - Arnold Fribble on 06/22/2003 13:05:41

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-23 : 05:04:18

Arnold,

you are a real Date Functions Guru.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources