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
463 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 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

831 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