Calculation of Number of days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculation of Number of days

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-30 : 14:13:54

I have a Hospital Utilization Report and my Client wants me to calculate the number of days.

Example

Date of Services from 1/1/08 - Date of Service Thru 01-05-2008. Which the total will be 5 days.

How would I calculate that?

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-30 : 14:25:04

SELECT DATEDIFF(day, '2008-01-01', '2008-01-05') AS no_of_days

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-30 : 14:29:23

DateDiff is the right function. But in your situation, be careful to understand if you're supposed to include the day in, the day out, or both. If your report needs to show both (1/1 - 1/5 = 5 days), then you'll need to add 1 to the result.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-30 : 14:33:46

SO then it would be

SELECT DATEDIFF(day, '2008-01-01', '2008-01-05',1)AS no_of_days

Is that correct?

quote:
Originally posted by tprupsis

DateDiff is the right function. But in your situation, be careful to understand if you're supposed to include the day in, the day out, or both. If your report needs to show both (1/1 - 1/5 = 5 days), then you'll need to add 1 to the result.

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-30 : 14:35:58

try this one...
SELECT DATEDIFF(day, '2008-01-01', '2008-01-05') + 1 AS no_of_days

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-30 : 14:43:44

Right. Would be nice if there were an optional parameter on DATEDIFF to include both the start and end period in the result.

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-01-30 : 14:45:54

ok that did work, but I am only getting 5 days, which I would with the way we wrote it.

What if I need to use field names instead is the right below?

SELECT DATEDIFF(day, 'DOSFROM', 'DOSTHRU') + 1

quote:
Originally posted by raaj

try this one...
SELECT DATEDIFF(day, '2008-01-01', '2008-01-05') + 1 AS no_of_days

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-30 : 14:49:42

quote:
Originally posted by tprupsis

Right. Would be nice if there were an optional parameter on DATEDIFF to include both the start and end period in the result.

That would not be a function, since a function returns only a single scaler value.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-30 : 14:52:04

quote:
Originally posted by werhardt

ok that did work, but I am only getting 5 days, which I would with the way we wrote it.

What if I need to use field names instead is the right below?

SELECT DATEDIFF(day, 'DOSFROM', 'DOSTHRU') + 1

quote:
Originally posted by raaj

try this one...
SELECT DATEDIFF(day, '2008-01-01', '2008-01-05') + 1 AS no_of_days


select
	datediff(day,a.DOSFROM,a.DOSTHRU)+1
from
	MyTable a

CODO ERGO SUM

raaj
Posting Yak Master

129 Posts

Posted - 2008-01-30 : 14:52:09

when u use this u get result 4 days:
SELECT DATEDIFF(day, '2008-01-01', '2008-01-05') AS no_of_days

when u use this u get result 5 days:
SELECT DATEDIFF(day, '2008-01-01', '2008-01-05') + 1 AS no_of_days

if u want to use fieldnames...dont put them in single quotes somthing like this:
SELECT DATEDIFF(day, DOSFROM, DOSTO) + 1 AS no_of_days

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-30 : 15:09:55

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by tprupsis

Right. Would be nice if there were an optional parameter on DATEDIFF to include both the start and end period in the result.

That would not be a function, since a function returns only a single scaler value.

CODO ERGO SUM

Actually, I meant add a new optional parameter to the DATEDIFF function. Something along the lines of DATEDIFF(day, StartDate, EndDate, True/False). If the 4th parameter was true, the start and end dates would be included, thus giving a result of 5 in the above example. If False (presumably the default), the result would be 4.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-30 : 15:25:13

quote:
Originally posted by tprupsis

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by tprupsis

Right. Would be nice if there were an optional parameter on DATEDIFF to include both the start and end period in the result.

That would not be a function, since a function returns only a single scaler value.

CODO ERGO SUM

Actually, I meant add a new optional parameter to the DATEDIFF function. Something along the lines of DATEDIFF(day, StartDate, EndDate, True/False). If the 4th parameter was true, the start and end dates would be included, thus giving a result of 5 in the above example. If False (presumably the default), the result would be 4.

It seems fairly clear what you should expect when you take a difference. The difference between 1 and 1 is zero, the difference between 1 and 5 is 4, just like regular integer arithmetic.

Adding 1 to a result is no more difficult using integer math than with an enhanced function. Notice below that they would be exactly the same number of characters to code:


select
	datediff(day,a.DOSFROM,a.DOSTHRU)+1,
	newddiff(day,a.DOSFROM,a.DOSTHRU,1)   -- Enhanced datediff
from
	MyTable a

CODO ERGO SUM

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-30 : 15:45:48

I agree it's no additional work to code. But date math is frequently different than simple arithmetic and I thought it would be a nice option to include one, both (or even none) of the end points. It's not really that big of a deal. Just a random thought was all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-30 : 17:35:37

http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources