Friends as you can see in the procedure I want to create a stored procedure which will tell me selected details of the person whose bithdate are coming with in the 15 days & 31 days i.e about 1 month of the current date.
so I had tried to make a use of two variables
as given below to check with the available data.
[font="System"]create procedure Birthdate_15days_old
@c_Month_of_birth INT = NULL,
@c_Date_of_birth INT = NULL
AS
set @c_Month_of_birth=datepart(mm,dateadd(dd,15,getdate()))
--advanced month part
set @c_Date_of_birth=datepart(dd,dateadd(dd,15,getdate()))
--advanced Date part
[/font]
as you can see I had inserted advanced values in the variables.
Now We will simply check with existing data field DOB available in person table.
checking with it in following manner with the field .
[font="System"]
--select @Month_of_birth,@Date_of_birth
SELECT P.PersonID, P.FIRSTNAME ,P.LASTNAME ,P.DOB
from person AS P
where
(((datepart(d,DOB)) <@c_Date_of_birth)) ---THIS IS FOR THE Date PART current
AND
(((datepart(mm,DOB))=@c_Month_of_birth)
and
(datepart(mm,DOB))=(datepart(mm,getdate())))
---FOR THE MONTH PART ,in case we are at the current date after 20 th day of month
[/font]
SO when we execute data returned NO ROW.
we had data as given below
[font="System"]
dob
-----------------------
2009-03-27 00:00:00.000
2009-03-28 00:00:00.000
2009-03-28 00:00:00.000
2009-03-29 00:00:00.000
2009-03-29 00:00:00.000
2009-03-30 00:00:00.000
2009-03-30 00:00:00.000
2009-03-31 00:00:00.000
2009-04-07 00:00:00.000
2009-04-25 00:00:00.000
(10 row(s) affected)
[/font]
I had made changes in the following line
[font="System"]SELECT P.PersonID, P.FIRSTNAME ,P.LASTNAME ,P.DOB
from person AS P
where
(((datepart(d,DOB)) <@c_Date_of_birth)) ---THIS IS FOR THE Date PART current
AND
(((datepart(mm,DOB))=@c_Month_of_birth)
OR--this is instead of AND
(datepart(mm,DOB))=(datepart(mm,getdate()))) [/font]
result is only one row
[font="System"]
PersonID FIRSTNAME LASTNAME DOB
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
24 Golden flower 2009-04-07 00:00:00.000
(1 row(s) affected)[/font]
while we expect some more rows , not returned.
Again changed are made in selecting date as follows
[font="System"]
--select @Month_of_birth,@Date_of_birth
SELECT P.PersonID, P.FIRSTNAME ,P.LASTNAME ,P.DOB
from person AS P
where
((((datepart(d,DOB)) <@c_Date_of_birth)) ---THIS IS FOR THE Date PART current
AND
((datepart(mm,DOB)) BETWEEN @c_Month_of_birth AND (datepart(mm,getdate()))))
[/font]
But NO RESULTS ARE RETURNED
Thanks in advance.