Solved

SQL Date Arithmetic

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

thanks!
Examples would be very appreciated.
0
Comment
Question by:Sylvania
  • 2
  • 2
  • 2
  • +4
12 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 2778446
Which Database server are you using?
0
 
LVL 4

Expert Comment

by:pellep
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.
0
 
LVL 10

Expert Comment

by:makerp
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
0
 

Author Comment

by:Sylvania
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:

"SELECT * FROM Table1 WHERE
CONVERT(datetime,MyDate,103)+3 < '" & Date & "'"

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

0
 
LVL 44

Expert Comment

by:bruintje
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...
0
 

Expert Comment

by:AZWolf7
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
DateAdd("m",1,MyDate)



0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Expert Comment

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

Expert Comment

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

Expert Comment

by:makerp
ID: 2779865
if Date is todays date then

"SELECT * FROM Table1 WHERE
(MyDate + 3) < CONVERT(datetime,GETDATE(),103)"

if date is a date string that your comparing against then  

"SELECT * FROM Table1 WHERE
(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.....

0
 
LVL 43

Accepted Solution

by:
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.

Syntax

date_ function (parameters)
where

date_ function

Specifies a date function.
The date functions are:

Date function      Description
DATEADD
(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.
DATEDIFF
(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.
DATENAME
(datepart, date)      Returns a character string representing the specified date part (datepart) of the specified date (date).
DATEPART
(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.
date

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

datepart

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.

Remarks

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.

Examples

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

go

Number of Minutes
------------------------
59


In SQL Server 6.0

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

go

Number of Minutes
------------------------
60


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

go

Current Date                
---------------------------
Feb 18 1995 11:46PM        

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

go

Month Name                    
------------------------------
February                      

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

go

Month Number
------------
2            
0
 

Author Comment

by:Sylvania
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.
0
 

Expert Comment

by:AZWolf7
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....

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

DATEADD (T-SQL)
Returns a new datetime value based on adding an interval to the specified date.

Syntax
DATEADD(datepart, number, date)

Arguments
datepart
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


number
Is the value used to increment datepart.
date
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.

0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now