• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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

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
marcoloppo
Asked:
marcoloppo
  • 7
  • 7
  • 2
2 Solutions
 
chapmandewCommented:
select * from tablename
where datediff(yy, birthdate, getdate()) between 10 and 20
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the column data type datetime or varchar?
do you want those EXACTLY 10 / 20 years, or between 10 years and 20 years?
0
 
marcoloppoAuthor Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marcoloppoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this eventually only sql server 2000? you posted in sql 2005 zone, so I suggested sql 2005 syntax.
0
 
marcoloppoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marcoloppoAuthor Commented:
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
 
chapmandewCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marcoloppoAuthor Commented:
Hi again,

Now it's other error message:

"Invalid object name 'today_date' "

Wiating, thanks.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marcoloppoAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marcoloppoAuthor Commented:
Great solution AngelIII, thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now