Solved

How to SELECT in a SQL Server Table people who's born 10 or 20 years ago (using birthdate DD/MM/AAAA)?

Posted on 2008-10-12
16
416 Views
Last Modified: 2010-08-05
Hi,
I'd like to know how to make a SELECT query to select people in a table who's born 10/20 years ago, in this table there is a birthdate column (DD/MM/AAAA)? I'm usign SQL Server and ASP.

Thank you
0
Comment
Question by:marcoloppo
  • 7
  • 7
  • 2
16 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
select * from tablename
where datediff(yy, birthdate, getdate()) between 10 and 20
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
is the column data type datetime or varchar?
do you want those EXACTLY 10 / 20 years, or between 10 years and 20 years?
0
 

Author Comment

by:marcoloppo
Comment Utility
Hi,

Thank you for your answer. The column is datetime type, but I need the results divided in:
Users born in 10 years ago, and in other query users born in 20 years ago, and it goes on...

But not users between 10 and 20 years ago, waiting.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
ok, what about this:
with today_date as ( select convert(datetime, convert(varchar(10), getdate(), 120), 120) value )

, agelist as ( select dateadd(year,-10, value) value from today_date

    union all  select dateadd(year,-20, value) from today_date

    union all  select dateadd(year,-30, value) from today_date

    union all  select dateadd(year,-40, value) from today_date

    union all  select dateadd(year,-50, value) from today_date

    union all  select dateadd(year,-60, value) from today_date

  )

select * from tablename

where birthdate in ( select value from dage_list ) 

Open in new window

0
 

Author Comment

by:marcoloppo
Comment Utility
Hi AngelIII,

Thank you for your answer, and congratulations for ur new certification! I tried the query you passed, changing the "tablename" to the name of my table and "birthdate" to the field's name in question, but i got an error when running the code in Enterprise Manager: "Incorrect syntax near the keyword AS".

I also tried changing "dage_list" to "agelist" but I got the same error.

Waiting, regards.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
is this eventually only sql server 2000? you posted in sql 2005 zone, so I suggested sql 2005 syntax.
0
 

Author Comment

by:marcoloppo
Comment Utility
Hi Again,

I tried in Microsoft SQL Server Management Studio Express and in Microsoft SQL Server 2000, sorry. May u pass this in 2000?

Thank you
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
here we go
select t.*

  from ( select dateadd(year,-10, convert(datetime, convert(varchar(10), getdate(), 120), 120) ) value from today_date

    union all  select dateadd(year,-20, convert(datetime, convert(varchar(10), getdate(), 120), 120) ) from today_date

    union all  select dateadd(year,-30, convert(datetime, convert(varchar(10), getdate(), 120), 120) ) from today_date

    union all  select dateadd(year,-40, convert(datetime, convert(varchar(10), getdate(), 120), 120) ) from today_date

    union all  select dateadd(year,-50, convert(datetime, convert(varchar(10), getdate(), 120), 120) ) from today_date

    union all  select dateadd(year,-60, convert(datetime, convert(varchar(10), getdate(), 120), 120) ) from today_date

  ) age_list

  join tablename t

    on t.birthdate = age_list.value

with today_date as ( select value )

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:marcoloppo
Comment Utility
Hi AngelIII,

In SQL Server Enterprise Manager - SQL Server 2000, I tried your code, just making the customization like follows below, but I got the error message:

" Incorret syntax near 'WITHtoday_date' "

Thanks, waiting.




SELECT     t .*

FROM         (SELECT     dateadd(year, - 10, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) value

                       FROM          today_date

                       UNION ALL

                       SELECT     dateadd(year, - 20, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 30, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 40, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 50, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 60, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date) age_list JOIN

                      sx_login t ON t .data_admissao = age_list.value WITH today_date AS

                          (SELECT     value)

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
just remove the last little bit:

SELECT     t .*
FROM         (SELECT     dateadd(year, - 10, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) value
                       FROM          today_date
                       UNION ALL
                       SELECT     dateadd(year, - 20, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
                       FROM         today_date
                       UNION ALL
                       SELECT     dateadd(year, - 30, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
                       FROM         today_date
                       UNION ALL
                       SELECT     dateadd(year, - 40, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
                       FROM         today_date
                       UNION ALL
                       SELECT     dateadd(year, - 50, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
                       FROM         today_date
                       UNION ALL
                       SELECT     dateadd(year, - 60, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
                       FROM         today_date) age_list JOIN
                      sx_login t ON t .data_admissao = age_list.value ;
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
sorry, that's a copy/paste/leftover :(
SELECT     t .*

FROM         (SELECT     dateadd(year, - 10, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) value

                       FROM          today_date

                       UNION ALL

                       SELECT     dateadd(year, - 20, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 30, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 40, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 50, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date

                       UNION ALL

                       SELECT     dateadd(year, - 60, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

                       FROM         today_date) age_list JOIN

                      sx_login t ON t .data_admissao = age_list.value

Open in new window

0
 

Author Comment

by:marcoloppo
Comment Utility
Hi again,

Now it's other error message:

"Invalid object name 'today_date' "

Wiating, thanks.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
review:
SELECT     t .*

FROM         (SELECT     dateadd(year, - 10, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)) value

  UNION ALL SELECT     dateadd(year, - 20, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

  UNION ALL SELECT     dateadd(year, - 30, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

  UNION ALL SELECT     dateadd(year, - 40, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

  UNION ALL SELECT     dateadd(year, - 50, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

  UNION ALL SELECT     dateadd(year, - 60, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))

   ) age_list 

JOIN sx_login t 

 ON t .data_admissao = age_list.value

Open in new window

0
 

Author Comment

by:marcoloppo
Comment Utility
Hi,

Thank you for your attention, now it worked, but I could see when I execute the query I got results like below:

User1 = 14/10/1998
User2 = 14/10/1988
User3 = 14/10/1978
User4 = 14/10/1968
User5 = 14/10/1958
User6 = 14/10/1948

But maybe I did not have express myself clearly or omitted it, I'm also needing that it lists to me the users until 0 and 10 years, 11 and 20 years, 21 and 30 years etc... because I won't use all this "clauses" together, sometimes I'll look for users between 1 and 10 years and between 21 and 30 years, do u understand?

Once thank you for your attention.
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
so, you want to give the "age" slice as parameter?
meaning: give me all the people aged between 10 and 20 years old?
SELECT t .*

  FROM sx_login t 

 WHERE t.data_admissao >= dateadd(years, -20, getdate())

   AND t.data_admissao <= dateadd(years, -10, getdate())

 

Open in new window

0
 

Author Closing Comment

by:marcoloppo
Comment Utility
Great solution AngelIII, thank you.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now