Birthday alert

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

  • What is the problem you're having?

  • 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

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

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

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

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

  • 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

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

  • 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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply