Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
513 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 2
16 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22698145
select * from tablename
where datediff(yy, birthdate, getdate()) between 10 and 20
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22698284
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
ID: 22703923
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22704096
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
ID: 22708601
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 143

Expert Comment

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

Author Comment

by:marcoloppo
ID: 22708817
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22708986
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
 

Author Comment

by:marcoloppo
ID: 22714053
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
ID: 22714090
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22714093
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
ID: 22715177
Hi again,

Now it's other error message:

"Invalid object name 'today_date' "

Wiating, thanks.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22715272
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
ID: 22717642
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 22717787
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
ID: 31505464
Great solution AngelIII, thank you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore 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.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

618 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