March 27, 2009 at 3:42 pm
Thank you for considering the following problem.
Hello everyone,
I'm trying to create PROCEDURE stored procedures to find the birthdate,
which would be in coming 15 days and 31 days from current date on a table.
Table -> Person
Field -> DOB
type -> datetime
Computed-> no
Nullable-> yes
data available
select P.dob from person as p
I had made the store procedure as follows
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)
Now created a stored procedure in following fashion
create procedure Birthdate_15days_old
---this is using the two variables
@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
set @c_Date_of_birth=datepart(dd,dateadd(dd,15,getdate()))--advanced Date
--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
OUTPUT
PersonID FIRSTNAME LASTNAME DOB
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
(0 row(s) affected)
some modifications are made
now using or at the store procedure
alter procedure Birthdate_15days_old
---this is using the two variables
@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
set @c_Date_of_birth=datepart(dd,dateadd(dd,15,getdate()))--advanced Date
--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)
or--this is instead of AND
(datepart(mm,DOB))=(datepart(mm,getdate())))
OUTPUT AFTER EXECUTING SAME
PersonID FIRSTNAME LASTNAME DOB
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
24 Golden flower 2009-04-07 00:00:00.000
(1 row(s) affected)
NOW USING BETWEEN IN STORED PROCEDURE
alter procedure Birthdate_15days_old
---this is using the two variables
@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
set @c_Date_of_birth=datepart(dd,dateadd(dd,15,getdate()))--advanced Date
--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()))))
OUTPUT
PersonID FIRSTNAME LASTNAME DOB
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
(0 row(s) affected)
please suggest soultions .
Thank you in advance.
March 27, 2009 at 3:49 pm
What is the problem you're having?
March 28, 2009 at 5:34 am
I'm also not sure about your problem, but maybe your solution is just this:
DECLARE @person TABLE (id INT, name VARCHAR(100), dob datetime)
INSERT INTO @person
SELECT 1, 'p1', '2009-03-27 00:00:00.000'
UNION SELECT 2, 'p2', '2009-03-28 00:00:00.000'
UNION SELECT 3, 'p3', '2009-03-28 00:00:00.000'
UNION SELECT 4, 'p4', '2009-03-29 00:00:00.000'
UNION SELECT 5, 'p5', '2009-03-29 00:00:00.000'
UNION SELECT 6, 'p6', '2009-03-30 00:00:00.000'
UNION SELECT 7, 'p7', '2009-03-30 00:00:00.000'
UNION SELECT 8, 'p8', '2009-03-31 00:00:00.000'
UNION SELECT 9, 'p9', '2009-04-07 00:00:00.000'
UNION SELECT 10, 'p10', '2009-04-25 00:00:00.000'
SELECT *, DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), dob)
FROM @person
WHERE DATEDIFF(DAY, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), dob) < 15
Greets
Flo
March 28, 2009 at 12:33 pm
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.
March 28, 2009 at 1:37 pm
Instead of posting a bunch of code that doesn't work, please explain exactly what you want to do.
It would also help it you posted the structure of the table, some sample data, and a sample of the expected otuput.
I suspect the problem you are trying to solve is far simpler than you think.
March 30, 2009 at 6:56 am
Can you just explain actually what you mean......i didnt get what you are telling about......and what do you mean by giving such a bunch of sp
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
March 30, 2009 at 8:50 am
KTTHOOL,
Please don't get frustrated or upset here. What we've asked for is you to explain what isn't working for you. We feel, a few of us, that you haven't presented a problem. You've stated something, and given code, but not explained what isn't working for you.
You can attack this two ways. One is to calculate the dates forward, meaning determine what is 15 days out (start date/end date) and 30 days out, or you can build a function to compare the dob's to the current date.
Be aware of time. Today is 3/30/09. It's 8:43am. so 15 days out is
- 4/14/09 00:00:00
- 4/14/09 23:59:59
The time 15 days out with getdate is 4/14/09 8:43am
When you calculate the dates, you might want to remove the times to handle this.
DECLARE @currdate datetime, @firstdate datetime
select @currdate = '3/15/09 8:43am'
select @firstdate = cast(
cast( year(@currdate) as varchar(4)) + '/' +
cast( month(@currdate) as varchar(2)) + '/' +
cast( day(@currdate) as varchar(2))
as datetime)
select @currdate, @firstdate
In terms of posting a question, please tell us the issue, You never made that clear. Don't give us code and expect that we will understand what you are thinking.
March 30, 2009 at 1:07 pm
I wrote a moving explanation and it wandered off into the ether, the general idea was this: Swap your between statement around. The code I used, slightly altered from yours is below.
if object_id('tempdb..#person')is not null drop table #person
if object_id('tempdb..#totemp')is not null drop table #totemp
create table #person
(personid int identity(1,1) not null,
firstname varchar(50) not null,
lastname varchar(50) not null,
dob smalldatetime not null)
insert into #person values('Lino','Dorotheos','2009-03-27 00:00:00.000')
insert into #person values('Apostolos','Maria','2009-03-28 00:00:00.000')
insert into #person values('Filippos','Dimitris','2009-03-28 00:00:00.000')
insert into #person values('Sotirios','Anastasios','2009-03-29 00:00:00.000')
insert into #person values('Yiannis','Theophylaktos','2009-03-29 00:00:00.000')
insert into #person values('Kyriakos','Angelos','2009-03-30 00:00:00.000')
insert into #person values('Yorgos','Xoán','2009-03-30 00:00:00.000')
insert into #person values('Nicolau','Kyriakos','2009-03-31 00:00:00.000')
insert into #person values('Xurxo','Tryphon','2009-04-07 00:00:00.000')
insert into #person values('Emmanouil','Spiridon','2009-04-25 00:00:00.000')
create table #totemp(dob smalldatetime null)
insert into #totemp values('2009-03-27 00:00:00.000')
insert into #totemp values('2009-03-28 00:00:00.000')
insert into #totemp values('2009-03-28 00:00:00.000')
insert into #totemp values('2009-03-29 00:00:00.000')
insert into #totemp values('2009-03-29 00:00:00.000')
insert into #totemp values('2009-03-30 00:00:00.000')
insert into #totemp values('2009-03-30 00:00:00.000')
insert into #totemp values('2009-03-31 00:00:00.000')
insert into #totemp values('2009-04-07 00:00:00.000')
insert into #totemp values('2009-04-25 00:00:00.000')
declare @c_Month_of_birth INT
declare @c_Date_of_birth INT
set @c_Month_of_birth=datepart(mm,dateadd(dd,15,getdate()))--advanced month
set @c_Date_of_birth=datepart(dd,dateadd(dd,15,getdate()))--advanced Date
--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
AND
datepart(mm,DOB) BETWEEN datepart(mm,getdate()) AND @c_Month_of_birth
April 4, 2009 at 2:55 am
Dear friends ,
Thank you for suggesting all the alternate ways to solve the problems.
One of my friends suggested me the following ways which is similar the way you explained.
He suggested the following steps.
1> Instead the hardcore 15 days value we can pass the number of days to stored procedure.
2> Now that would be done in the following query.
the query may look like this
select * from person where datediff(d,getdate(),dob)> @nos_of_days
@nos_of_days -> the passed value of days to be found out could be 15,12,30,40 etc.
The problem is that the SQL SERVER 2005 stores in yy/dd/mm format.
So when our dob which will give larger amount of diffreence like 12/12/1999 and currentdate i.e. 27/3/2009 which could be result in higher days like greater then 1500 days, not valid.
The solution is the append the current year i.e. 2009 for all the dob fields such that we can find out only the difference between month & date , years are made same so we don't have problem in line. we have to get it in similar format the query may look like this .
select convert(varchar(12),dob+cast(year(getdate())as varchar),105) from person
3> substract currentdate using (GETDATE()) from that modified dob whose current year is same.
(well obvious that we had made it same in step two)
4> check if it's greater then our desired values , the query may look like this.
select * from person where datediff(d,getdate(),dob)< @nos_of_days
well this is nice one based on the magic of CONVERT & CAST functions .
I had tried the following queries to make the working idea clear in mind.
select cast(year(getdate())as varchar) from person
select cast(year(dob)as varchar) from person
output (all the current year)
2009
select convert(varchar(12),dob,105) from person
-- to convert in dd/mm/yy format which will be used for Comparing the dates , the output is now same just giving the year nothing else.
27-03-2009
Now main problem is
select convert(varchar(12), dob+cast(year(getdate())as varchar),103) from person
/* output
28/03/2118
as you can see the desired output was just to append the 2009 on the dob field but it's not working.
So could you ,please suggest the steps to overcome for implement the stored procdeure, Please.
Thanks in advance.
April 4, 2009 at 5:07 am
KTTHOOL (4/4/2009)
The problem is that the SQL SERVER 2005 stores in yy/dd/mm format.
No, the problem is that you think it does, and it does not. SQL SERVER 2005 stores dates as datetime. Even your own DOB column which you defined in your first post:
Table -> Person
Field -> DOB
type -> datetime
Computed-> no
Nullable-> yes
There's no need to convert it to a string or anything else, just use the built-in DATETIME functions:
-- make up some sample DOB data
DECLARE @person TABLE (id INT, name VARCHAR(100), dob DATETIME)
INSERT INTO @person
SELECT 1, 'p1', '1948-12-30 00:00:00.000'
UNION SELECT 2, 'p2', '1958-12-31 00:00:00.000'
UNION SELECT 3, 'p3', '1968-01-01 00:00:00.000'
UNION SELECT 4, 'p4', '1982-01-02 00:00:00.000'
UNION SELECT 5, 'p5', '2004-04-04 00:00:00.000' -- Happy Birthday to me!
UNION SELECT 6, 'p6', '2005-04-16 00:00:00.000' -- 12 days away
UNION SELECT 7, 'p7', '2006-04-25 00:00:00.000' -- 21 days away
UNION SELECT 8, 'p8', '2007-05-05 00:00:00.000'
UNION SELECT 9, 'p9', '2008-05-20 00:00:00.000'
UNION SELECT 10, 'p10', '2009-05-25 00:00:00.000'
-- show how the algorith works
DECLARE @Today DATETIME, -- allows you to change the "current date" for testing
@WarningDays INT
SET @Today = DATEDIFF(DAY, 0, GETDATE()) -- set the time-stripped "current date"
SET @WarningDays = 15
SELECT *,
DATEDIFF(YEAR, dob, @Today), -- number of year boundaries crossed since DOB
DATEADD(yy, DATEDIFF(YEAR, dob, @Today), dob), -- add to DOB to give birthday this year
DATEDIFF(dd, @Today, DATEADD(yy, DATEDIFF(YEAR, dob, @Today), dob)) -- compare with current date to yield number of days until birthday
FROM @person
id name dob YearBounds Birthday DaysToBD
----------- ---- ----------------------- ----------- ----------------------- -----------
1 p1 1948-12-30 00:00:00.000 61 2009-12-30 00:00:00.000 270
2 p2 1958-12-31 00:00:00.000 51 2009-12-31 00:00:00.000 271
3 p3 1968-01-01 00:00:00.000 41 2009-01-01 00:00:00.000 -93
4 p4 1982-01-02 00:00:00.000 27 2009-01-02 00:00:00.000 -92
5 p5 2004-04-04 00:00:00.000 5 2009-04-04 00:00:00.000 0
6 p6 2005-04-16 00:00:00.000 4 2009-04-16 00:00:00.000 12
7 p7 2006-04-25 00:00:00.000 3 2009-04-25 00:00:00.000 21
8 p8 2007-05-05 00:00:00.000 2 2009-05-05 00:00:00.000 31
9 p9 2008-05-20 00:00:00.000 1 2009-05-20 00:00:00.000 46
10 p10 2009-05-25 00:00:00.000 0 2009-05-25 00:00:00.000 51
-- use the algorithm
DECLARE @Today DATETIME, -- allows you to change the "current date" for testing
@WarningDays INT
SET @Today = DATEDIFF(DAY, 0, GETDATE()) -- set the time-stripped "current date"
SET @WarningDays = 15
SELECT *,
DATEDIFF(dd, @Today, DATEADD(yy, DATEDIFF(YEAR, dob, @Today), dob)) AS DaysToBD
FROM @person
WHERE DATEDIFF(dd, @Today, DATEADD(yy, DATEDIFF(YEAR, dob, @Today), dob)) BETWEEN 0 AND @WarningDays
id name dob DaysToBD
----------- ---- ----------------------- -----------
5 p5 2004-04-04 00:00:00.000 0
6 p6 2005-04-16 00:00:00.000 12
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply