DETECT AGE USING DATEDIFF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DETECT AGE USING DATEDIFF

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-01 : 23:15:11

Andris writes "In my database I have 3 fields: day, month, year which determine persons birthday, birthmonth and birthyear.

How could I search my database for people with different ages. For instance, to show all employes, who are more than 30 years old?

I tried something like this (simple example):

SELECT * WHERE DATEDIFF("yyyy", (day & "." & month & "." & year), DATE()) > 30

but this returns an error.

I would be happy to hear Your opinions about it.

PS. I have SQL Server 2000 (Enterprise Edition), and MS Windows NT SP6a."

Nazim
A custom title

1408 Posts

Posted - 2002-01-02 : 00:39:44

SELECT * tablename WHERE
DATEDIFF(yyyy, cast(day & "-" & month & "-" & year as datetime),
getdate()) > 30

HTH

----------------------------
Anything which Doesn't Kills you Makes you Stronger

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-01-05 : 11:32:39

DATEDIFF counts boundaries, not complete elapsed periods, so it alone is not sufficient for age.


DATEPART(yyyy, GETDATE()) - year -
  CASE
    WHEN month > DATEPART(m, GETDATE()) THEN 1
    WHEN month = DATEPART(m, GETDATE())
      AND day > DATEPART(d, GETDATE()) THEN 1
    ELSE 0
  END

It's trying to spell 'Nietzsche' that kills me

barmalej
Starting Member

40 Posts

Posted - 2002-01-05 : 13:14:08

Another approach which seems to be fast in general and is especially simple when burthdays are in the form of datetime:

select * from YourTable
where YourTable.Birthdate < dateadd(yy, -30, getdate() )

If you have burthdays in the Year-Month-Day form then naturally replace YourTable.Birthdate
by
cast(day & "-" & month & "-" & year as datetime)

Good luck

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-05 : 22:19:35

quote:
SELECT * WHERE DATEDIFF("yyyy", (day & "." & month & "." & year), DATE()) > 30

In your original attempt, posted above, this is in VB syntax. To put it into TSQL syntax, use + instead of & for concatenation, and use getdate() instead of date() to return today's date.

So try it again. And you need to put in a FROM tablename clause before the WHERE
--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 01/05/2002 22:21:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources