1

I have a table that contains a birthday in the format (YYYYMMDD) eg '19951118'

What is the most efficient way to findout if the user has turned 18?

I add my solution in the post instead of a comment: SELECT * from Users where DATEADD(YEAR,18,BirthDay) > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

user2916136
  • 13
  • 1
  • 4
  • I solved it like this SELECT * from Users where DATEADD(YEAR,18,BirthDay) > DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) – user2916136 Nov 18 '13 at 12:28
  • 3
    If you care about efficiency why are you storing dates as strings? `date` is 3 bytes compared with 8 bytes for `char(8)`. Plus if you use the correct datatype you don't need to cast or run the risk of nonsense dates. – Martin Smith Nov 18 '13 at 12:28
  • @MartinSmith: I assume that he's just confusing the output of his `datetime` column with a _format_. – Tim Schmelter Nov 18 '13 at 12:35
  • @TimSchmelter - Dunno what you base that assumption on. `YYYYMMDD` is not how datetimes are displayed by default and there seems to be a never ending supply of questions asked by people not using correct date/time datatypes. – Martin Smith Nov 18 '13 at 12:42
  • @MartinSmith: `declare @BirthDate datetime = '19951118'` – Tim Schmelter Nov 18 '13 at 12:45
  • 2
    @user2916136 - I just noticed your comment immediately under the question. `where DATEADD(YEAR,18,BirthDay) >` is not efficient. That isn't sargable. Use the correct datatype for the column and `WHERE BirthDay <= DATEADD(YEAR,-18,GETDATE())` – Martin Smith Nov 18 '13 at 12:47
  • Learn how to calculate age [here](http://stackoverflow.com/questions/19853970/adding-days-to-date-in-sql/19856002#19856002) or [here](http://stackoverflow.com/questions/18588672/count-the-amount-of-years-from-a-certain-date-sql/18593995#18593995) – t-clausen.dk Nov 18 '13 at 13:12
  • Well the problem is that i cant change how the data is stored in the Table and it is stored on the format and the reason i asked how to get better performance is mainly to speed thins up as much as possible without changing how the data is stored. – user2916136 Nov 18 '13 at 13:42

2 Answers2

2

If you store date of birth as a varchar then

WHERE DateDiff(month,CONVERT(datetime,'19951118',112),getdate())>=18*12

SQLFiddle demo

valex
  • 23,966
  • 7
  • 43
  • 60
1

Assuming your dateofbirth is char(8), this will perform well on a large table because there is no calculation on the column birthday.

WHERE 
 REPLACE(CONVERT(CHAR(10), DATEADD(YEAR,-18,CURRENT_TIMESTAMP), 126), '-', '') 
 >= birthday
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92