If you would like to compare the
BirthDate
with some range of dates, you need to use a simple trick: replace the year part of [BirthDate] with current year and compare it with these dates.
But how to replace the year part in
BirthDate
?
In
MS SQL Server 2012 use the
DATEFROMPARTS[
^] method. In the
previous versions of MS SQL Server, you need to write custom function to create date from parts of BithDate. For example:
CREATE FUNCTION [dbo].[CREATEDATE]
(
@aYear INT = 0,
@aMonth INT = 0,
@aDay INT = 0
)
RETURNS DATETIME
AS
BEGIN
DECLARE @Result DATETIME
SET @Result = '1900-01-01'
IF (@aYear>=1900 AND @aMonth>0 AND @aDay>0)
BEGIN
SELECT @Result = DATEADD(mm,(@aYear-1900)* 12 + @aMonth - 1,0) + (@aDay-1)
END
RETURN @Result
END
And finally, the query should looks like this one:
DECLARE @begDate DATETIME
DECLARE @endDate DATETIME
SET @begDate = GETDATE()
SET @endDate = DATEADD(d, 7, @begDate)
SELECT [pName], [Birthdate]
FROM PEOPLE
WHERE [dbo].CREATEDATE(YEAR(GETDATE()), MONTH([Birthdate]), DAY([Birthdate])) BETWEEN @begDate AND @endDate
[EDIT]
In MS Access:
SELECT [pName], [Birthdate]
FROM PEOPLE
WHERE DateSerial(Year(Date()), Month([BirthDate]) ,Day([BirthDate])) BETWEEN Date() and DateAdd("d", 7, Date());
[/EDIT]
That's all!