Strip off year from date field

Question

Post reply

Strip off year from date field

Murtaza Millwala

SSC-Addicted

Points: 410
More actions
October 30, 2006 at 8:57 pm

#153266

hello,
I need to do an extract to select members whose Birthdays will be coming after 2 weeks and within the 2 week period.
Example, if today is the 1st of a month, then find all memebrs whose birthday falls in from 14th to 28th of that month.
Have been using th DOB field in the member table but dont know how to strip off the year?
Could someone help. thanks.
Regards,
Murtaza
Dragon
Melbourne-Australia

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Hope it will help:

declare @d TABLE (BirthDate datetime)

INSERT INTO @d (BirthDate)

SELECT '1955-10-25'

UNION

SELECT '1960-11-25'

UNION

SELECT '1965-11-05'

UNION

SELECT '1970-11-15'

UNION

SELECT '1970-12-20'

UNION

SELECT '1970-11-01'

UNION

SELECT '1970-10-31'

UNION

SELECT '1970-11-30'

SELECT BirthDate, DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE()), BirthDate) NextBirthday

FROM @d

_____________
Code for TallyGenerator

Murtaza Millwala SSC-Addicted Points: 410 More actions · Answer 2

Thanks, Your reply helped a lot.

i was able to understand how the datediff function works. Basaically its adding their age to their birth year to get their birthdates for the current year.

Brilliant.

So I dont need to strip off the year basically. that saves some other complexities.

Thanks very much.

Murtaza

Dragon
Melbourne-Australia

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

How does this work if the Birthdate for the current year is already passed???

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Actually, this isn't next birthday. It is the birthday for current year, no matter whether it is in future or past.

I'm not sure whether it is possible to get next birthday with such a simple formula... I'd only know how to do it with CASE.

(modified Sergiy's SQL)

declare @d TABLE (BirthDate datetime)

INSERT INTO @d (BirthDate)

SELECT '1955-10-25'

UNION

SELECT '1960-11-25'

UNION

SELECT '1965-11-05'

UNION

SELECT '1970-10-15'

UNION

SELECT '1970-12-20'

UNION

SELECT '1970-11-01'

UNION

SELECT '1970-10-31'

UNION

SELECT '1970-11-30'

SELECT Q.BirthDate, CASE WHEN Q.ThisBirthday < GETDATE() THEN DATEADD(YY,1,Q.ThisBirthday) ELSE Q.ThisBirthday END as NextBirthday

FROM

(SELECT BirthDate, DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE()), BirthDate) as ThisBirthday

FROM @d) as Q

EDIT: Murtaza, you may need to use something like this because of year's end - you want to find those that have birthday in January next year. The original version would display January of the actual year instead.

Murtaza Millwala SSC-Addicted Points: 410 More actions · Answer 5

Thanks for the tip Vladen,

using sergiy's code was able to use it to suit my purpose, but your warning came just in time. Doesnt seem to get data from the next year.

Since this is the basic purpose of my package:

SELECT

distinct memberID,Title,FirstName,LastName,DOB,Email,Country

FROM

dbo.members

WHERE

DATEADD(YY, DATEDIFF(YY, DOB, GETDATE()), DOB) BETWEEN GETDATE()+14 AND GETDATE()+28

The query failed when i used +59 and +90 to get birthdays in jan.. returned no results..

Will try and modify it again asap.

Would like to say Thanks to everyone whos contributed in this post.

regards,

Murtaza

Dragon
Melbourne-Australia

Vladan SSC-Insane Points: 22072 More actions · Answer 6

Hi Murtaza,

this is how you could do it:

/*parameters*/

DECLARE @datefrom DATETIME, @dateto DATETIME

SET @datefrom=GETDATE()+14

SET @dateto = GETDATE()+28

/*query*/

SELECT Q.memberID,Q.Title,Q.FirstName,Q.LastName,Q.DOB,Q.Email,Q.Country

FROM

(SELECT memberID,Title,FirstName,LastName,DOB,Email,Country,

DATEADD(yy,DATEDIFF(yy,BirthDate,0),BirthDate) as DMOB /*day and month of birth*/

FROM dbo.members) as Q

WHERE DMOB >= DATEADD(yy,DATEDIFF(yy,@datefrom,0),@datefrom)

AND DMOB < DATEADD(yy,DATEDIFF(yy,@dateto,0),@dateto)

However, it still does not cover all situations . It works properly if both dates are in the same year (no matter whether this or next), but if @datefrom is in this year and @dateto in next year, nothing is returned. Will have to elaborate on that, too.

Problem with this solution is that I tried to stick to your original requirement - that is, to strip away year from the date of birth... but then you can't compare correctly at the end of year, because January 1 is not bigger than December 31. I think we should start from the other end and work in opposite direction... I will think about it and post later again if I have time.

Sergiy SSC Guru Points: 110209 More actions · Answer 7

Guys, there is a rule - if it becomes too complex then you've chosen wrong direction.

SELECT BirthDate,

CASE

WHEN DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE()), BirthDate) > GETDATE

THEN DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE()), BirthDate)

ELSE DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE())+1, BirthDate)

END NextBirthday

FROM @d

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 8

That's it, Sergiy... I said right at the beginning that I would know how to do it with CASE, but then started to ponder about your solution that wasn't using CASE and tried to find some similar to that. Right now I was testing some possibilities and was just coming to the conclusion that CASE will be simplest after all. Thanks for confirming that, good to see that it wasn't just my incompetence to write it properly

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Or an even simpler solution (maybe it even saves a few cpu cycles)...

declare @d TABLE (BirthDate datetime)

INSERT INTO @d (BirthDate)

SELECT '1955-10-25'

UNION

SELECT '1960-11-25'

UNION

SELECT '1965-11-05'

UNION

SELECT '1970-10-15'

UNION

SELECT '1970-12-20'

UNION

SELECT '1970-11-01'

UNION

SELECT '1970-10-31'

UNION

SELECT '1970-11-30'

SELECT BirthDate, DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE())

+ CASE WHEN DATEADD(YY, DATEDIFF(YY, BirthDate, GETDATE()), BirthDate) > GETDATE()

THEN 0 ELSE 1 END, BirthDate) AS NextBirthday

FROM @d

Murtaza Millwala SSC-Addicted Points: 410 More actions · Answer 10

hello Friends,

Thanks for all the help that you guys have given me.

Finall got it working right to suit the purpose. Now it will get birthdays from next year as well.

Changing the variables of datefrom and dateto to 27 and 80 days respectively will get all members whose birthdays starting from 1st dec 2006 to 22nd or 23rd jan 2007 (whatever the 80 days count upto).

DECLARE

@DateFrom DATETIME, @DateTo DATETIME

SET

@DateFrom=GETDATE()+14

SET

@DateTo = GETDATE()+28

SELECT DISTINCT MemberID,Title,FirstName,LastName,DOB,Email,Country

CASE

WHEN

DATEADD(YY, DATEDIFF(YY, DOB, GETDATE()), DOB) >= @DateFrom

THEN DATEADD(YY, DATEDIFF(YY, DOB, GETDATE()), DOB)

ELSE DATEADD(YY, DATEDIFF(YY, DOB, GETDATE())+1, DOB)

END

NextBirthday

FROM

dbo.members

WHERE

(DOB IS NOT NULL AND RTRIM(LTRIM(DOB)) <> '')

AND

CASE WHEN DATEADD(YY, DATEDIFF(YY, DOB, GETDATE()), DOB) >= @DateFrom

THEN DATEADD(YY, DATEDIFF(YY, DOB, GETDATE()), DOB)

ELSE DATEADD(YY, DATEDIFF(YY, DOB, GETDATE())+1, DOB) END BETWEEN @DateFrom AND @DateTo

ORDER

BY NextBirthday DESC

using the CASE statement worked, but had to apply it in both the select and where clause to be able to get the birth dates from the next year.

(if you remove it from the where then it will get dates starting from 1st dec 2006 to 30 Nov 2007.)

Thanks to everyone.

Regards,

Murtaza

Dragon
Melbourne-Australia