Help Needed For Date field (Age) Calculation In SQl query

Help Needed For Date field (Age) Calculation In SQl query

am 30.08.2007 12:18:34 von Dinesh

Hi experts,

I am working on SQL server 2005 reporting services and i am getting a
problem in writting a query.

Situation is given below.

There is one table in database Named Child

Now i have to find the All childrens whoes Age is 13 years Base on
Some given parameter.

If User select Augus 2007 then It has to calculate the Childs who born
in August 1994 And if he select September Then query

should show only those child Who born in September 1994 and so
on..... And use can select another year month also like

August 2009 ...


I am writting the following query

Select Child_Name, DOb from Child
where ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1
And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)

If i know already month and year then i can write easily parameter1
and parameter2 But since these are comming from user so i m not
finding how to handle this.


Now please suggest me what i have to write in Where statement I think
a lot but not getting any idea about it.



Any help wil be appriciated.

Regards
Dinesh

Re: Help Needed For Date field (Age) Calculation In SQl query

am 30.08.2007 15:36:52 von Roy Harvey

If I understand correctly, you want to select on a specific month of
birth that is 13 years prior to a month provided as a starting point.

create table Born (Name varchar(20), dob datetime)

INSERT Born values('Nancy', '19940704')
INSERT Born values('Ralph', '19940804')
INSERT Born values('Edgar', '19940904')

declare @d datetime
set @d = '20070801'

SELECT @d, *
FROM Born
WHERE DATEDIFF(month,dob,@d) = (13 * 12)

DROP TABLE Born

Roy Harvey
Beacon Falls, CT

On Thu, 30 Aug 2007 03:18:34 -0700, Dinesh
wrote:

>Hi experts,
>
>I am working on SQL server 2005 reporting services and i am getting a
>problem in writting a query.
>
>Situation is given below.
>
>There is one table in database Named Child
>
>Now i have to find the All childrens whoes Age is 13 years Base on
>Some given parameter.
>
>If User select Augus 2007 then It has to calculate the Childs who born
>in August 1994 And if he select September Then query
>
>should show only those child Who born in September 1994 and so
>on..... And use can select another year month also like
>
>August 2009 ...
>
>
>I am writting the following query
>
>Select Child_Name, DOb from Child
>where ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1
>And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)
>
>If i know already month and year then i can write easily parameter1
>and parameter2 But since these are comming from user so i m not
>finding how to handle this.
>
>
>Now please suggest me what i have to write in Where statement I think
>a lot but not getting any idea about it.
>
>
>
>Any help wil be appriciated.
>
>Regards
>Dinesh

Re: Help Needed For Date field (Age) Calculation In SQl query

am 31.08.2007 10:17:57 von Dinesh

On Aug 30, 6:36 pm, Roy Harvey wrote:
> If I understand correctly, you want to select on a specific month of
> birth that is 13 years prior to a month provided as a starting point.
>
> create table Born (Name varchar(20), dob datetime)
>
> INSERT Born values('Nancy', '19940704')
> INSERT Born values('Ralph', '19940804')
> INSERT Born values('Edgar', '19940904')
>
> declare @d datetime
> set @d = '20070801'
>
> SELECT @d, *
> FROM Born
> WHERE DATEDIFF(month,dob,@d) = (13 * 12)
>
> DROP TABLE Born
>
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 30 Aug 2007 03:18:34 -0700, Dinesh
> wrote:
>
>
>
> >Hi experts,
>
> >I am working on SQL server 2005 reporting services and i am getting a
> >problem in writting a query.
>
> >Situation is given below.
>
> >There is one table in database Named Child
>
> >Now i have to find the All childrens whoes Age is 13 years Base on
> >Some given parameter.
>
> >If User select Augus 2007 then It has to calculate the Childs who born
> >in August 1994 And if he select September Then query
>
> >should show only those child Who born in September 1994 and so
> >on..... And use can select another year month also like
>
> >August 2009 ...
>
> >I am writting the following query
>
> >Select Child_Name, DOb from Child
> >where ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1
> >And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)
>
> >If i know already month and year then i can write easily parameter1
> >and parameter2 But since these are comming from user so i m not
> >finding how to handle this.
>
> >Now please suggest me what i have to write in Where statement I think
> >a lot but not getting any idea about it.
>
> >Any help wil be appriciated.
>
> >Regards
> >Dinesh- Hide quoted text -
>
> - Show quoted text -

Thanks Roy Harvey
Yes for your reply. I got my solution with your help.
Regards
Dinesh