DateADD gives error

Question

Hello All,
I have this:
Declare @Tenure Table
(EmployeeID int,
LastName VarChar(255)
,DateOfBirth Date
)

Insert into @Tenure
Select EmployeeID, LastName, DateAdd(YY,DateOfBirth,GetDate())
From dbo.Employees

It gives this error:

Msg 8116, Level 16, State 1, Line 43
Argument data type date is invalid for argument 2 of dateadd function.

What is the solution?

Respectfully,
Blue Sky

Answer 1

Hi @CouldsInSky ,

Welcome to Microsoft Q&A!

DATEADD is one function which adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

Syntax:

DATEADD (datepart , number , date )

For example:

SELECT  DATEADD(year,-20,GetDate())  --2001-02-02   

It is recommended for you to post CREATE TABLE statements for your table(dbo.Employees) together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Meanwhile you could refer below example:

drop table if exists dbo.Employees  
  
create table dbo.Employees  
(EmployeeID int,  
LastName VarChar(255)  
,Age int)  
  
insert into dbo.Employees values  
(1,'Tom',20)  
  
Declare @Tenure Table  
(EmployeeID int,  
LastName VarChar(255)  
,DateOfBirth Date  
)  
  
Insert into @Tenure  
Select EmployeeID, LastName, DateAdd(YY,-1*Age,GetDate())  
From dbo.Employees  
  
select * from @Tenure  

Output:

EmployeeID	LastName	DateOfBirth  
1	Tom	2001-02-02  

Or you could use another function DateDiff if you would like to count the age of the employee as below:

drop table if exists dbo.Employees  
  
create table dbo.Employees  
(EmployeeID int,  
LastName VarChar(255)  
,DateOfBirth date)  
  
insert into dbo.Employees values  
(1,'Tom','2001-01-02')  
  
Declare @Tenure Table  
(EmployeeID int,  
LastName VarChar(255)  
,Age int  
)  
  
Insert into @Tenure  
Select EmployeeID, LastName, DateDiff(YY,DateOfBirth,GetDate())  
From dbo.Employees  
  
select * from @Tenure  

Output:

EmployeeID	LastName	Age  
1	Tom	20  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

DATEADD adds an integer value to a date.

It appears you are trying to get the age, which would be DATEDIFF().

https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15

Answer 3

Hello team,

With what you said then DateAdd function requires an integer for the second argument. Since I entered a date for second argument, I get an error.

Would you kindly please confirm this?

Respectfully,

Blue Sky