I was recently asked to add a field to a SQL report that adds days to an existing date field.  I thought I’d share the process of how to use the DATEADD function in SQL.

 

DATEADD()

A DATEADD() function is placed in the SELECT statement and consists of the following: DATEADD(datepart, number, date)

Click the tabs to view definitions:

The Datepart represents the date format you would like add or subtract from in the DATEADD function.  The datepart can represent a year, month, week, hour, minute or second.  The graph below is a complete list of datepart options.

Can be a positive or negative number depending on whether you want to add or subtract time from a specified date.

Date represents the date field in your table that will be used to add or subtract time from in the DATEADD function.

 

DATEADD Function in SQL Server

Table Courtesy of W3Schools.com

 

Example

Now that we understand the DATEADD() function, let’s put it to use.  I’ll use the DATEADD function I created to provide a real life example.

 

I was asked to create two separate fields that added 20 days and 30 days to a patient’s admission date.  The DATEADD functions I created were:

 DATEADD(day,20,AdmissionDate)  DATEADD(day,30,AdmissionDate)

I set the datepart to day, number to 20 & 30 (which represented the number of days) and the date field in my database table was called AdmissionDate.

 

A brief example of what this query would look like is:

SELECT AdmissionDate, DATEADD(day,20,AdmissionDate) AS  Day20AdmitDate, DATEADD(day,30,AdmissionDate) AS Day30AdmitDate  FROM PatAdmissions

A sample result would look like:

DATEADD Function in SQL Server Sample

 

About 

I help small businesses use technology to grow and solve problems.   I live in Little Rock, Arkansas and spend my free time with my wife, 2 kids and dog.