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
433 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
ID: 22698145
select * from tablename
where datediff(yy, birthdate, getdate()) between 10 and 20
0
 
LVL 142

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
 
LVL 142

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 142

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 142

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

 

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 142

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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 142

Assisted Solution

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 32
SQL Express connecting form remote error 26 7 40
SQL Backup skipping a few tables 7 36
Help with simplifying SQL 6 50
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

15 Experts available now in Live!

Get 1:1 Help Now