Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Add and Subtract Dates using DATEADD in SQL Server

MSSQLTips author Tim Cullen By:   |   Read Comments (7)   |   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: 9/30/2011


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.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Monday, August 06, 2012 - 12:26:53 PM - Gene Wirchenko Read The Tip

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


Wednesday, March 13, 2013 - 4:41:05 AM - Ramakrishna Read The Tip

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


Wednesday, March 13, 2013 - 4:57:00 AM - Ramakrishna Read The Tip

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)

 


Monday, March 18, 2013 - 12:52:35 PM - Dan Read The Tip

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)


Friday, May 31, 2013 - 5:02:23 PM - Martin Read The Tip

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

 


Sunday, June 02, 2013 - 7:43:34 PM - Tim Cullen Read The Tip

Good evening, Martin:

 

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

 

Tim


Wednesday, June 19, 2013 - 6:19:57 AM - chester Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.