SQL Date Arithmetic

Posted on 2000-05-04
Last Modified: 2008-03-17
How does SQL date arithmetic works in a SELECT?
(Like Date + 1 for tomorrow, or other possible calculation?)

Examples would be very appreciated.
Question by:Sylvania
  • 2
  • 2
  • 2
  • +4
LVL 44

Expert Comment

ID: 2778446
Which Database server are you using?

Expert Comment

ID: 2778447
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.
LVL 10

Expert Comment

ID: 2778458
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 2778639
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)...

LVL 44

Expert Comment

ID: 2778672
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...

Expert Comment

ID: 2778710
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


Expert Comment

ID: 2779747
DATEADD, DATEDIFF, DAY etc functions are available in SQL Server. For further help refer SQL Server books online

Expert Comment

ID: 2779752
DATEADD, DATEDIFF, DAY etc functions are available in SQL Server. For further help refer SQL Server books online
LVL 10

Expert Comment

ID: 2779865
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.....

LVL 43

Accepted Solution

TimCottee earned 50 total points
ID: 2779876
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

Author Comment

ID: 2780590
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.

Expert Comment

ID: 2781076
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.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question