SQL Date Arithmetic

How does SQL date arithmetic works in a SELECT?
(Like Date + 1 for tomorrow, or other possible calculation?)

Examples would be very appreciated.
Who is Participating?
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
AZWolf7, a point about the intervaltype, in SQL 6.5 the intervaltype is day  not "d" e.g.,

SELECT MyDate,DATEADD(day,4,MyDate) FROM MyTable

returns Mydate and Mydate + 4 days

Anyway, here is the full text from T-SQL help.


date_ function (parameters)

date_ function

Specifies a date function.
The date functions are:

Date function      Description
(datepart, number, date)      Produces a date by adding an interval to a specified date. The result is a datetime value equal to the date plus the number of date parts. If the date parameter is a smalldatetime value, the result is also a smalldatetime. You can use DATEADD to add seconds or milliseconds to a smalldatetime value, but the addition is meaningful only where the resulting date changes by at least 1 minute.
(datepart, date1, date2)      Returns the number of datepart "boundaries" crossed between two specified dates. The method of counting crossed boundaries makes the result given by DATEDIFF consistent across all datatypes such as minutes, seconds, and milliseconds. DATEDIFF takes three parameters. The first is a datepart; the second and third are dates, either datetime or smalldatetime values. The result is a signed integer value equal to the number of datepart boundaries crossed between date2 minus date1. For example, the number of weeks between Sunday, January 4 and Sunday, January 11, is 1. DATEDIFF produces an error if the result is out range for integer values. For milliseconds, the maximum number that can be represented is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number that can be represented is approximately 68 years.If smalldatetime values are used, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are set to 0 for calculating the difference.
(datepart, date)      Returns a character string representing the specified date part (datepart) of the specified date (date).
(datepart, date)      Returns an integer representing the specified date part (datepart) of the specified date (date).
GETDATE()      Returns the current system date and time in the SQL Server standard internal format for datetime values. GETDATE does not take any parameters.

Can be either the GETDATE function, a character string in date format, or the name of a datetime column.


Is a parameter used with DATEADD, DATEDIFF, DATENAME, and DATEPART. The following table lists the date parts, the abbreviations recognized by SQL Server, and the acceptable values:

Date part      Abbreviation      Values
year      yy      1753-9999
quarter      qq      1-4
month      mm      1-12
day of year      dy      1-366
day      dd      1-31
week      wk      1-53
weekday      dw      1-7 (Sun.-Sat.)
hour      hh      0-23
minute      mi      0-59
second      ss      0-59
millisecond      ms      0-999
If the year is given with two digits, <50 is the next century and >=50 is this century. So "25" is "2025," and "50" is "1950."
Milliseconds can be preceded by either a colon or a period. If preceded by a colon, the number means thousandths of a second. If preceded by a period, a single digit means tenths of a second, two digits mean hundredths of a second, and three digits mean thousandths of a second. For example, "12:30:20:1" means 20 and one-thousandth seconds past 12:30; "12:30:20.1" means 20 and one-tenth seconds past 12:30.
Use the datetime datatype for dates after January 1, 1753. When entering datetime values, always enclose them in quotation marks. Use char or varchar for earlier dates.

SQL Server automatically converts between character and datetime values when necessary ¾ for example, when you compare a character value with a datetime value.
The date part weekday or dw returns the day of the week (Sunday, Monday, and so on) when used with datename. It returns a corresponding number (Sunday = 1, Saturday = 7) when used with datepart. Using weekday or dw with DATEADD and DATEDIFF is not logical and produces spurious results. Use day or dd instead.
Because smalldatetime is accurate only to the minute, when a smalldatetime value is used with either datename or datepart seconds and milliseconds are always 0.


Date functions can be used in the select list or in the WHERE clause of a query.
Use the datetime datatype only for dates after January 1, 1753. When entering datetime values, always enclose them in quotation marks. Store as character data for earlier dates. SQL Server recognizes a wide variety of date styles. For more information about date and time data, see the Datatypes topic and CONVERT Function.


A.      DATEADD Function

This example prints a listing of a "timeframe." This timeframe represents the existing publication date plus 21 days.

SELECT timeframe = DATEADD(day, 21, pubdate)
      FROM titles

B.      DATEDIFF Function

This example determines the difference in days between the current date and the publication date.

SELECT newdate = DATEDIFF(day, pubdate, getdate())
      FROM titles

C.      DATEDIFF Function Changes in SQL Server 6.0

This example shows the difference in results of the new DATEDIFF function in SQL Server 6.0.
In earlier releases:

SELECT "Number of Minutes" = DATEDIFF(minute, 'jan 1 1995 22:30:16', 'jan 1 1995 23:30:15')


Number of Minutes

In SQL Server 6.0

SELECT "Number of Minutes" = DATEDIFF(minute, 'jan 1 1995 22:30:16', 'jan 1 1995 23:30:15')


Number of Minutes

The difference is minor; however, it accurately reflects that 60 "minute boundaries" were crossed.

D.      DATENAME and DATEPART Functions

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE( ) as well as some other date functions.

SELECT "Current Date" = getdate()


Current Date                
Feb 18 1995 11:46PM        

SELECT "Month Name" = DATENAME(month, getdate())


Month Name                    

SELECT "Month Number" = DATEPART(month, getdate())


Month Number
Which Database server are you using?
What kind of database and what platform are you working on? SQL dialects differ sligthly between different systems. Some might for instance implement a function similar to the DataAdd function in VB.
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

in access i just do things like this

select * from files where date_field >= (Date() + 3)

so this gets all files with a date_field greater then today + 3 days.

in sql server you have to use the convert function i.e.

select * from files where date_field >= (CONVERT(datetime,GETDATE(),103) + 3)

the convert function takes 3 params, the data type you want to convert to, the string you want to convert and the style you want reterned . 103 is dd/mm/yyyy.

hope ive helped
SylvaniaAuthor Commented:
I am using SQL Server, but I don't want to convert a string to a Datetime.
I already have a datetime, and I want to do arithmetic operations with it.
When I do your code, I get an operand type clash (int vs datetime).
I'll show you my query:

CONVERT(datetime,MyDate,103)+3 < '" & Date & "'"

(MyDate is a field (datetime) in Table1 table on SQL Server)...

you can lookup in T-SQL or books online, what the appropriate type has to be thought it was 109 instead of 103 but i''m not sure, got no install here ....

but lookup the convert function and you should get a whole list of convert and formatting for dates function types...
Microsoft Databases (SQL Server and Access) both support the DateAdd functions. Not sure about Oracle though. Here's the syntax:

DateAdd(IntervalType, Number, FirstDate)

Such as:
DateAdd("d",1,MyDate)  or

DATEADD, DATEDIFF, DAY etc functions are available in SQL Server. For further help refer SQL Server books online
DATEADD, DATEDIFF, DAY etc functions are available in SQL Server. For further help refer SQL Server books online
if Date is todays date then

(MyDate + 3) < CONVERT(datetime,GETDATE(),103)"

if date is a date string that your comparing against then  

(MyDate + 3) < CONVERT(datetime,'"&your_date_string&"',103)"

you dont have to runh convert on existing cols in the table i.e. your date field. but you will have to run it on dates you pass in etc.....

SylvaniaAuthor Commented:
The first line of your comment is exactly what I needed!
I do use SQL Server 6.5, but I didn't know it was relevant to specify it... sorry guys.
Thanks for your help: "day" works just fine in my case.
Just an FYI, I was scared I would have to go back and change all my old code. ;)

But here is the docs for the T-SQL (and the same is for Access, BTW...

You can use "day", "dd", or "d". How you want the format depends on this setting....


Returns a new datetime value based on adding an interval to the specified date.

DATEADD(datepart, number, date)

Is the parameter that specifies on which part of the date to return a new value. The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms

Is the value used to increment datepart.
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. For information about specifying dates, see datetime and smalldatetime.
If you specify only the last two digits of the year, values that are less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values that are greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.