Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Add and Subtract Dates using DATEADD in SQL Server


By:   |   Read Comments (9)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Dates

Problem

Date manipulation is a common scenario when retrieving or storing data in a SQL Server database. There are several functions that are available and in this tip we look at how to use the DATEADD function.

Solution

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

The format of the DATEADD function is as follows:

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

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

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

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/R2 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.


Below are examples of using the DATEADD function. 
For all of these examples, the parameter @Date = "2011-09-23 15:48:39.2370000".

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:39.2385000

SECOND SELECT DATEADD(SECOND,1500,@Date) 2011-09-23 16:13:39

MINUTE SELECT DATEADD(MINUTE,15,@Date) 2011-09-23 16:03:39

HOUR SELECT DATEADD(HOUR,-3,@Date) 2011-09-23 12:48:39

WEEKDAY SELECT DATEADD(WEEKDAY,-2,@Date) 2011-09-21 15:48:39

WEEK SELECT DATEADD(WEEK,4,@Date) 2011-10-21 15:48:39

DAY SELECT DATEADD(DD,4,@Date) 2011-09-27 15:48:39

DAYOFYEAR SELECT DATEADD(DY,14,@Date) 2011-10-07 15:48:39

MONTH SELECT DATEADD(MM,-4,@Date) 2011-05-23 15:48:39

QUARTER SELECT DATEADD(Q,-2,@Date) 2011-03-23 15:48:39

YEAR
SELECT DATEADD(YYYY,-5,@Date) 2006-09-23 15:48:39

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
  • Keep in mind that the NANOSECOND and MICROSECOND units of time can only be used with SQL Server 2008 and later
  • Also keep in mind that there is more than one way to execute a query and get the same results. There will be tips in the future that will discuss other options
  • Read about the DATEADD function
  • Learn more about the date and time data types available in SQL Server 2008
  • For other date formats refer to this tip: Date and Time Conversions Using SQL Server


Last Update:






About the author





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, August 08, 2016 - 4:03:39 PM - Jerry Sharp Back To Top

 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

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

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 02, 2013 - 7:43:34 PM - Tim Cullen Back To Top

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

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

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

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

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 06, 2012 - 12:26:53 PM - Gene Wirchenko Back To Top

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).


Learn more about SQL Server tools