Add and Subtract Dates using DATEADD in SQL Server

By:   |   Comments (17)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Dates


Problem

Date manipulation is a common scenario when retrieving or storing data in a Microsoft SQL Server database. There are several date functions (DATENAME, DATEPART, DATEADD, DATEDIFF, etc.) that are available and in this tutorial, we look at how to use the DATEADD function in SQL queries, stored procedures, T-SQL scripts, etc. for OLTP databases as well as data warehouse and data science projects.

Solution

The DATEADD function simply allows you to add or subtract the specified number of units of time to a specified date/time value.

SQL Server DATEADD Function

The T-SQL syntax of the DATEADD function is as follows:

DATEADD(<Unit of time>, <Units>, <Input Date>)

-- Syntax to add 5 days to September 1, 2011 (input date) the function would be
DATEADD(DAY, 5, '9/1/2011')

-- Syntax to subtract 5 months from September 1, 2011 (input date) the function would be
DATEADD(MONTH, -5, '9/1/2011')

Using DATEADD Function and Examples

  1. Add 30 days to a date SELECT DATEADD(DD,30,@Date)
  2. Add 3 hours to a date SELECT DATEADD(HOUR,-3,@Date)
  3. Subtract 90 minutes from date SELECT DATEADD(MINUTE,-90,@Date)
  4. Check out the chart to get a list of all options

Date Formats and Units of Time

A thing to note is that the date format can be any date format that SQL Server recognizes such as:

  • 9/1/2011
  • 9/1/2011 12:30
  • 9/1/2011 12:30:999
  • 2011-09-01
  • 2011-09-01 12:30
  • etc...

Here are the units of time, the SQL Server versions in which they can be used, and abbreviations that can be used instead of the full unit of time name:

Units of Time Available in SQL Server
Unit of time 2000/2005 2008 + Abbreviations
NANOSECOND No Yes ns
MICROSECOND No Yes mcs
MILLISECOND Yes Yes ms
SECOND Yes Yes ss, s
MINUTE Yes Yes mi, n
HOUR Yes Yes hh
WEEKDAY Yes Yes dw, w
WEEK Yes Yes wk, ww
DAY Yes Yes dd, d
DAYOFYEAR Yes Yes dy, y
MONTH Yes Yes mm, m
QUARTER Yes Yes qq, q
YEAR Yes Yes yy, yyyy

The table above reveals that there are some units of time that cannot be used with earlier versions of SQL Server. SQL Server 2008 and later introduced new date/time data types: DATETIME2, TIME, and DATETIMEOFFSET. The MICROSECOND and NANSECOND units of time were introduced as well, but cannot be used in earlier versions of SQL Server. Another thing to keep in mind is that you can't use the seconds unit of time with the DATE data type.

SQL Server DATEADD Function Examples

For all of these Transact-SQL examples, the parameter @Date = "2011-09-23 15:48:39.2370000", which we consider our current date.  We can test the SQL commands as follows:

DECLARE @Date datetime2 = '2011-09-23 15:48:39.2370000'

SELECT DATEADD(NANOSECOND,150000,@Date)
Examples of Using the DATEADD Function
Unit of time Query Result
NANOSECOND SELECT DATEADD(NANOSECOND,150000,@Date) 2011-09-23 15:48:39.2371500
MICROSECOND SELECT DATEADD(MICROSECOND,150000,@Date) 2011-09-23 15:48:39.3870000
MILLISECOND SELECT DATEADD(MILLISECOND,1500,@Date) 2011-09-23 15:48:40.7370000
SECOND SELECT DATEADD(SECOND,1500,@Date) 2011-09-23 16:13:39.2370000
MINUTE SELECT DATEADD(MINUTE,15,@Date) 2011-09-23 16:03:39.2370000
HOUR SELECT DATEADD(HOUR,-3,@Date) 2011-09-23 12:48:39.2370000
WEEKDAY SELECT DATEADD(WEEKDAY,-2,@Date) 2011-09-21 15:48:39.2370000
WEEK SELECT DATEADD(WEEK,4,@Date) 2011-10-21 15:48:39.2370000
DAY SELECT DATEADD(DD,4,@Date) 2011-09-27 15:48:39.2370000
DAYOFYEAR SELECT DATEADD(DY,14,@Date) 2011-10-07 15:48:39.2370000
MONTH SELECT DATEADD(MM,-4,@Date) 2011-05-23 15:48:39.2370000
QUARTER SELECT DATEADD(Q,-2,@Date) 2011-03-23 15:48:39.2370000
YEAR
SELECT DATEADD(YYYY,-5,@Date) 2006-09-23 15:48:39.2370000

Example 1

A practical use of DATEADD is to return a user-friendly length of time that has elapsed between two times, like how long it takes for a student to complete an exam.

Below we are also using the DATEDIFF function to find the difference between the start and end time in seconds, then adding the number of seconds to 01/01/1900, which can be represented as a 0.

DECLARE @StartTime DATETIME = '2011-09-23 15:00:00'
       ,@EndTime   DATETIME = '2011-09-23 17:54:02'
       
SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,@StartTime, @EndTime),0), 108) as ElapsedTime
-- the output would be 02:54:02

Example 2

Another use would be when retrieving data based on a period.  The procedure could pass in a start date and a number of days to retrieve data.

DECLARE @StartDate DATETIME = '2005-07-01'
DECLARE @Units INT = 7

SELECT * 
FROM AdventureWorks2008R2.Production.WorkOrder
WHERE StartDate BETWEEN @StartDate AND DATEADD(DAY, @Units, @StartDate)
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, May 17, 2022 - 8:00:44 AM - Arrie Schoeman Back To Top (90094)
Thank you that solve my problem. I test it with 5 , 30, 65, 365 and 730 days and it give me the correct date WITH NO ERRORS.

Thanks so much.
The next question will come soon..

Tuesday, May 17, 2022 - 6:30:16 AM - Greg Robidoux Back To Top (90092)
Hi Arrie,

can you just pass the interval value for each record, instead of hard coding the value.

SELECT DATE_ADD(base_date, INTERVAL frequency DAY);

-Greg

Monday, May 16, 2022 - 5:56:55 PM - Arrie Schoeman Back To Top (90090)
Hi Greg
This article will not solve my problem.
ID base_date frequency next_insp
1 2021-07-22 90 ??
2 2021-05-15 180 ??
3 2022-01-03 365 ??
4 2022-04-25 70 ??

As you can see is that all the frequency days are not the same. I cant use "INTERVAL" because Interval is a fix amount of days.. When you add say 25 days to all the dates.

Test it self
Arrie

Monday, May 16, 2022 - 8:51:08 AM - Greg Robidoux Back To Top (90089)
Hi Arrie,

This article is for how to do this in SQL Server.

Check out this one for MySQL - https://www.w3schools.com/SQl/func_mysql_date_add.asp

-Greg

Monday, May 16, 2022 - 7:20:30 AM - Arrie Schoeman Back To Top (90088)
Can you solve this please. ID Primary key, table asset_type VARCHAR(30), Today_date DATE, base_date DATE, frequency INT, next_insp DATE.
in the DEFAULT i need to add frequency (days) to the base_date. ('base_date' + 'frequency').
The frequency for every row is different, and can be anything from 30(days) to 730 ( 2 years)
As I enter days within the month say 2022-03-10 + 20 I get 2022-03-30 witch is correct. But if I enter a number like 65 (days) there is an error and in the error it display 20220370.
I tried ADDDATE, DATE_ADD but nothing work.
I need to store this next_insp date.

I hope you can help. I am using MySQL Workbench. I am new to MySQL if you can keep it simple for me to understand please.
Thank you so much

Thursday, April 22, 2021 - 1:13:06 PM - Joe F Celko Back To Top (88590)
The only display format allowed for dates in the ANSI ISO standards for SQL is based on ISO – 8601. It is "yyyy-mm-dd", with the dashes we made this decision so that the display be unambiguous and could always be parsed correctly.

Wednesday, January 29, 2020 - 8:59:37 AM - Greg Robidoux Back To Top (84034)

Hi Louis, you are correct.  A positive number to add and a negative number to subtract.  The examples above show both adding and subtracting from the date which is what is done in the HOUR example.

-Greg


Tuesday, January 28, 2020 - 6:09:09 PM - Louis De Grazia Back To Top (84021)

Add 3 hours to a date SELECT DATEADD(HOUR,-3,@Date)

Shouldn't this be +3?


Monday, August 8, 2016 - 4:03:39 PM - Jerry Sharp Back To Top (43078)

 Hi Tim,

I have an ERP system that generates time records (Oracle table) that are then extracted via SQL and interfaced to another system for payroll processing.

In short what is happening is scanning employee badge creates two records....one is the basic time stamp and the other (identical time stamp also) but referencing the account OVH which is unaccountable time.  This identical timestamping is considered a duplicate in the upstream system and they delete the OVH record.  The upstream SME's say other teams add a milli second to the record for OVH to ensure no duplicate.  I need some help in how to select from the tables but add the millisecond.  Any help would be really appreciated.  Thanks and my yahoo account is best to respond to.

Code snippet where st_time is the record we would want to modify and have 1 millisecond additional:

          select  etg.etg_rcd_ak, sys.user_id user_id, sys.sdf_sysur_005 starsID,                                           
                        sys.sdf_sysur_004 home_div,
                        'LABOR' trans_type,
                        ' ' scan_type,
                        lbh.lbh_auto_key batch_header_auto_key,
                        '1' batch_code,                                                                       
                        '1' charge_qty,                                                                       
                        to_char(wtl.start_time,'YYYYMMDD')st_date,
                                                                                               to_char(wtl.start_time,'HH24:MI:SS') st_time,
                        '' clock_record_autokey,
                        substr(wtl.machine,-7) dev_used,
                        to_char(wtl.wtl_auto_key) labor_record_autokey,
                        woo.si_number, to_char(wot.sequence) task
                from    wo_task_labor wtl, sys_users sys, wo_task wot, wo_operation woo, etg_labor etg, labor_batch_header lbh, labor_batch_detail lbd
                where   sys.sysur_auto_key = wtl.sysur_auto_key
                        and etg.wtl_auto_key = wtl.wtl_auto_key
                        and wtl.wot_auto_key = wot.wot_auto_key
                        and wot.woo_auto_key = woo.woo_auto_key
                        and etg.lbd_auto_key is not null
                        and etg.lbd_auto_key = lbd.lbd_auto_key
                        and lbd.lbh_auto_key = lbh.lbh_auto_key
                        and etg.sent = 'F'

 


Friday, November 20, 2015 - 10:53:31 PM - Asintha Back To Top (39115)

Sir,

how can I subtract 3 or more datetime value in SQL report expression

ex: Fields!TimeOut.value- Fields!TimeIn.value- Fields!Breaktime.value

when I try this it gave me at error. only 2 fields accepting. like below

Fields!TimeOut.value- Fields!TimeIn.value

if I add the third one it gives me an error.


Wednesday, June 19, 2013 - 6:19:57 AM - chester Back To Top (25477)

hi,how am i going to select the records per week? example i want to select the 1st week of july where the start date is sunday?this is for my weekly report but i dont know how to start it. i can post my code here for getting the records daily if it is possible to use for weekly:

 

 

 Dim dt As Date = DateTimePicker1.Text

        If ComboBox1.Text = "Central Reservation Report" Then

            If RadioButton1.Checked = True Then

                CheckBox1.Enabled = False

 

                Dim rpt As New rsvD() 'The report you created.

                Dim MyCommand As New SqlCommand()

                Dim myDS As New DScentralrsv() 'The DataSet you created.

                Dim myDA As New SqlDataAdapter()

                Dim cn As New SqlConnection(ConnectString())

 

                Try

 

                    MyCommand.Connection = cn

                    MyCommand.CommandText = "Select irsno,date,industry,totalamount from maintable where date='" + dt + "'"

                    MyCommand.CommandType = CommandType.Text

                    myDA.SelectCommand = MyCommand

                    myDA.Fill(myDS, "maintable")

 

                    MyCommand.CommandText = "Select irsno from roomtbl"

                    myDA.Fill(myDS, "roomtbl")

 

                    myDS.EnforceConstraints = False

                    rpt.SetDataSource(myDS)

                    CrystalReportViewer1.ReportSource = rpt

                Catch Excep As Exception

                    MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                End Try

 

            End If

        End If


Sunday, June 2, 2013 - 7:43:34 PM - Tim Cullen Back To Top (25244)

Good evening, Martin:

 

Can you give me a little more clarification?  I'm not sure of what you ultimately want to do.

 

Tim


Friday, May 31, 2013 - 5:02:23 PM - Martin Back To Top (25224)

Hi, im a new guy in the sql programming so please i need your help, i have a view with the code attached, my problem is that i must to change the date (2013-05-30) by the date of the system and i dont know how can you help me? Thanks

 

CODE :

SELECT   GETDATE() as MYDATE,debcode AS Relation, DebtorNumber AS OffsetNumber, ci.cmp_name AS OffSetName, (CASE WHEN bt.Type = 'S' THEN bt.ValueDate ELSE bt.InvoiceDate END)
                      AS InvDate, bt.DueDate AS DueDate, bt.InvoiceNumber, bt.SupplierInvoiceNumber, (CASE WHEN bt.AmountDC > 0 AND
                      bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN bt.Type = 'S' AND bt.AmountDC < 0 THEN - bt.AmountDC ELSE NULL END) END) AS Debit,
                      (CASE WHEN bt.AmountDC < 0 AND bt.Type = 'W' THEN - bt.AmountDC ELSE (CASE WHEN bt.Type = 'S' AND bt.AmountDC > 0 THEN bt.AmountDC ELSE NULL END)
                      END) AS Credit, ROUND((CASE WHEN DATEDIFF(dd, bt.DueDate, { d   '2013-05-30' }) BETWEEN 0 AND 30 AND
                      bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, { d '2013-05-30' }) BETWEEN 0 AND 30 AND
                      bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2) AS T1, ROUND((CASE WHEN DATEDIFF(dd, bt.DueDate, { d '2013-05-30' }) BETWEEN 31 AND 60 AND
                      bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, { d '2013-05-30' }) BETWEEN 31 AND 60 AND
                      bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2) AS T2, ROUND((CASE WHEN DATEDIFF(dd, bt.DueDate, { d '2013-05-30' }) BETWEEN 61 AND 90 AND
                      bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, { d '2013-05-30' }) BETWEEN 61 AND 90 AND
                      bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END) END), 2) AS T3, ROUND((CASE WHEN DATEDIFF(dd, bt.DueDate, { d '2013-05-30' }) > 90 AND
                      bt.Type = 'W' THEN bt.AmountDC ELSE (CASE WHEN DATEDIFF(dd, bt.ValueDate, { d '2013-05-30' }) > 90 AND bt.Type = 'S' THEN - bt.AmountDC ELSE NULL END)
                      END), 2) AS T4, (CASE WHEN bt.Type = 'W' THEN bt.AmountDC ELSE - bt.AmountDC END) AS AmountDC, cc.ClassificationID AS Classification, bt.Description,
                      bt.EntryNumber, (CASE WHEN bt.Type = 'W' THEN bt.AmountTC ELSE - bt.AmountTC END) AS AmountT

 


Monday, March 18, 2013 - 12:52:35 PM - Dan Back To Top (22849)

i

f the year is more than 2063 then you can't do your calculations, dont ask me why or how but thats what i have observed.

 

------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT

 

DATEDIFF(SECOND,'1970-01-01 00:00:00','2038-01-19 03:14:07'

)

 

 

output

 

--------

 

2147483647

 

-----------------------------------------------------------------------------------------

 

SELECT

 

 

DATEDIFF(SECOND,'1970-01-01 00:00:00','2078-01-19 03:14:07'

 

)

 

 

Msg 535

,Level 16,State 0,

Line 1

The

 

datedifffunction resulted in an overflow. The number of dateparts separating two date/time instances is too large.Trytousedatediffwith a less precise datepart.

-------------------------------------------------------------------------------------------

 

how to handle this and viseversa ( out put from date to int , int to date)


Wednesday, March 13, 2013 - 4:57:00 AM - Ramakrishna Back To Top (22775)

SELECT

DATEDIFF(SECOND,'1970-01-01 00:00:00','2038-01-19 03:14:07'

)

output

--------

2147483647

-----------------------------------------------------------------------------------------

SELECT

 

DATEDIFF(SECOND,'1970-01-01 00:00:00','2078-01-19 03:14:07'

)

Msg 535

,Level 16,State 0,

Line 1

The

datedifffunction resulted in an overflow. The number of dateparts separating two date/time instances is too large.Trytousedatediffwith a less precise datepart.

-------------------------------------------------------------------------------------------

how to handle this and viseversa ( out put from date to int , int to date)

 


Wednesday, March 13, 2013 - 4:41:05 AM - Ramakrishna Back To Top (22774)

select

DATEADD(s, 2147483647,'1970-01-01 00:00:00')

-------------------------------------------------------------

2038-01-19 03:14:07.000

 

if i want to get more than integer value...how to validate this...

for examble...2078-01-19 03:14:07.000


Monday, August 6, 2012 - 12:26:53 PM - Gene Wirchenko Back To Top (18935)

There are errors in the DATEADD example table. 1) SELECT DATEADD(MILLISECOND,1500,@Date) does not give 2011-09-23 15:48:39.2385000. It gives 2011-09-23 15:48:40.7370000. 2) All of the entries after millisecond are missing the fractional seconds (.2385000).















get free sql tips
agree to terms