SELECT based on Anniversary - 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

Transact-SQL (2000)

SELECT based on Anniversary

Author

Topic

helderjsm
Starting Member

2 Posts

Posted - 2006-05-29 : 09:02:09

Hi, i have a customers table and i have a birthday column which is of type date, and i want to select all customers WHERE birthday is BETWEEN day x of month y and day x1 of month y1, regardless of the year.

Thanks,
Helder Magalhães.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-29 : 09:07:25

Somthing like this

Select * From Customer Where Birthdate Between 'x-y-z' And 'x1-y1-z' ??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-29 : 09:08:28

[code]declare @x int,
@y int,
@x1 int,
@y1 int,
@from datetime,
@to datetime

select @x = 15, @y = 4
select @x1 = 1, @y1 = 7

select @from = dateadd(day, @x - 1, dateadd(month, @y - 1, 0))
select @to = dateadd(day, @x1 - 1, dateadd(month, @y1 - 1, 0))
select *
from Customers
where dateadd(year, 1900 - year(bdate), bdate) >= @from
and dateadd(year, 1900 - year(bdate), bdate) <= @to[/code]

helderjsm
Starting Member

2 Posts

Posted - 2006-05-29 : 19:41:15

Worked Like a charm.

Thanks khtan at first i didn't undestood why using the dateadd function but i read some posts and understood its because of performance reasons.

chiragkhabaria that's not what i wanted but thanks anyway :)

Anyways just out of curiosity the query ended uplike this:

DECLARE @from datetime,
@to datetime

SELECT @from = dateadd(day,day(getdate()) - 1,dateadd(month,month(getdate()) - 1,0))
SELECT @to = dateadd(day,@NrOfDays,@from)

SELECT tblCustomers.CustomerId, tblCustomers.CustomerName, tblCustomers.CustomerAddress, tblCustomers.CustomerCity, tblCustomers.CustomerPostalC,
tblCustomers.CustomerPhone, tblCustomers.CustomerMobile, tblCustomers.CustomerBirth, SUM(tblFiles.Total) AS CustomerDebit,
MAX(tblFiles.DateTime) AS CustomerLastPayment
FROM tblCustomers INNER JOIN
tblFiles ON tblCustomers.CustomerId = tblFiles.CustomerId
GROUP BY tblCustomers.CustomerId, tblCustomers.CustomerName, tblCustomers.CustomerAddress, tblCustomers.CustomerCity,
tblCustomers.CustomerPostalC, tblCustomers.CustomerPhone, tblCustomers.CustomerMobile, tblCustomers.CustomerBirth

HAVING dateadd(year, 1900 - year(tblCustomers.CustomerBirth), tblCustomers.CustomerBirth) >= @from
AND dateadd(year, 1900 - year(tblCustomers.CustomerBirth), tblCustomers.CustomerBirth) <= @to

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources