0

I am trying to check the Age of a user from the Date Of Birth to make sure Age is between 16 and 80, When the data is inserted into the table

The Date of Birth is a DATE datatype

I have tired looked for multiple solutions and none of them check it when the data is inserted

I have found this help out article to get the current Date with only the Date: https://learnsql.com/cookbook/how-to-get-the-current-date-without-time-in-t-sql/#:~:text=To%20get%20the%20current%20date%20and%20time%20in%20SQL%20Server,20%2010%3A22%3A34%20.

But i can't seem to get it to work with the CHECK function when creating the table

Moti
  • 47
  • 1
  • 7
  • @forpas I was considering dupe-hammering against that post too, but then realized that it only shows how to *calculate* the age, which is not necessary here, all we need is some `WHERE` clauses – Charlieface Jun 26 '22 at 16:26

2 Answers2

2

The other answer does not correctly deal with partial years.

For example, given GETDATE() as 20220626 and a DateOfBirth as 20060627, a calculation based on DATEDIFF will return TRUE. This is because DATEDIFF just counts the number of date boundaries that pass between the two dates.

Another issue is that it cannot use indexes.

A more correct caluclation uses DATEADD against the current date:

WHERE DateOfBirth <= DATEADD(year, -16, CAST(GETDATE() AS date))
  AND DateOfBirth >  DATEADD(year, -80, CAST(GETDATE() AS date));

You also need CAST AS date otherwise you end up with conversion issues

db<>fiddle

Charlieface
  • 52,284
  • 6
  • 19
  • 43
1

Something like:

DATEDIFF(year, DateOfBirth, GETDATE()) BETWEEN 16 AND 80

Not final code, but could be inserted in your scenario hopefully :)

Anynamer
  • 334
  • 1
  • 6
  • Thanks, here's my usage of it => DateOfBirth DATE NOT NULL CHECK(DATEDIFF(year, DateOfBirth, GETDATE()) BETWEEN 16 AND 80), – Moti Jun 26 '22 at 16:09
  • @Moti It's a really bad idea to use `GETDATE()` in a check constraint, and it makes little sense to, as it may work today and fail tomorrow, but the row would have already been inserted. – Charlieface Jun 26 '22 at 20:29