Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Birthday alert Expand / Collapse
Author
Message
Posted Friday, March 27, 2009 3:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 28, 2011 4:31 AM
Points: 10, Visits: 18
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.
Post #685432
Posted Friday, March 27, 2009 3:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:31 AM
Points: 33,155, Visits: 15,286
What is the problem you're having?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #685436
Posted Saturday, March 28, 2009 5:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #685537
Posted Saturday, March 28, 2009 12:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 28, 2011 4:31 AM
Points: 10, Visits: 18
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.

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


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 .

--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

SO when we execute data returned NO ROW.
we had data as given below

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)

I had made changes in the following line

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())))


result is only one row

PersonID FIRSTNAME LASTNAME DOB
----------- -------------------------------------------------- -------------------------------------------------- -----------------------
24 Golden flower 2009-04-07 00:00:00.000

(1 row(s) affected)

while we expect some more rows , not returned.

Again changed are made in selecting date as follows


--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()))))


But NO RESULTS ARE RETURNED

Thanks in advance.
Post #685607
Posted Saturday, March 28, 2009 1:37 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 3,135, Visits: 11,471
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.
Post #685616
Posted Monday, March 30, 2009 6:56 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, June 1, 2009 6:06 AM
Points: 518, Visits: 354
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

+++BLADE+++
Post #686044
Posted Monday, March 30, 2009 8:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:31 AM
Points: 33,155, Visits: 15,286
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #686189
Posted Monday, March 30, 2009 1:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:49 AM
Points: 273, Visits: 442
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

Post #686413
Posted Saturday, April 4, 2009 2:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 28, 2011 4:31 AM
Points: 10, Visits: 18
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.
Post #690403
Posted Saturday, April 4, 2009 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 7,175, Visits: 13,621
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #690428
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse