Subtracting two dates and coming up with a number

[Jesse O]

I have two date fields, start_date and end_date.

I'd like to subtract the two dates, and come up with a number (the
number of difference between the two dates).

What function is there to do this? I haven't been able to find anything
in BOL.




Start_date = 6/1/03
End_date = 6/8/03

End_date - start_date = 7

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Kass]

Re: Subtracting two dates and coming up with a number

Jesse,

If there are no time portions to the dates, you can

Select cast(End_date - Start_date as int)

That will still work with time portions, but will round up to the next int
if the partial day difference is 12 hours or more. If you want the
calendar day difference between two datetimes with time portions,
you can use datediff():

select datediff(day, Start_date, End_date)

The Search tab of Books Online is helpful for this kind of thing.
If you enter
date subtract
and have "Match similar words" selected, you should get very few
results, one of which is DATEDIFF and one of which is - (Subtract).

Steve Kass
Drew University

Jesse O wrote:
[color=blue]
>I have two date fields, start_date and end_date.
>
>I'd like to subtract the two dates, and come up with a number (the
>number of difference between the two dates).
>
>What function is there to do this? I haven't been able to find anything
>in BOL.
>
>
>
>
>Start_date = 6/1/03
>End_date = 6/8/03
>
>End_date - start_date = 7
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!
>
>[/color]