Datediff doubt

Question

Datediff doubt

gaurav-404321

SSCommitted

Points: 1593
More actions
March 30, 2010 at 5:38 am

#231270

I am using datediff functionm to calculate the days span between two dates
SET @StartDate=cast('2010-01-26 00:00:00.000' as datetime)
SET @EndDate=cast('2010-02-25 00:00:00.000' as datetime)
SET @TotalDaysInSalaryPeriod= DATEDIFF(dd,@StartDate,@EndDate)
this is giving 30 as output.
But this should be 31. Am i making some mistake?
Whats the behavior of datediff function? is it excluding startdate or end date or both are including

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

It's not excluding anything. It's telling you the difference in days between the two dates.

Despite the fact that if you counted from the start date to the end date you'd end up with 31, the difference between them is 30.

In the same way as the difference between 1 and 5 is 4, despite the fact that if you count from 1 to 5 there are 5 numbers 😀

Dugi SSCoach Points: 17998 More actions · Answer 2

gaurav-404321 (3/30/2010)
I am using datediff functionm to calculate the days span between two dates
SET @StartDate=cast('2010-01-26 00:00:00.000' as datetime)
SET @EndDate=cast('2010-02-25 00:00:00.000' as datetime)
SET @TotalDaysInSalaryPeriod= DATEDIFF(dd,@StartDate,@EndDate)
this is giving 30 as output.
But this should be 31. Am i making some mistake?
Whats the behavior of datediff function? is it excluding startdate or end date or both are including

The EndDate is the end of the 2010-02-24, so if you want the results 31 you should set the EndDate as 2010-02-25 23:59:59.999 and it means the all daytime for 2010-02-25!

DECLARE @STARTDATED DATETIME = '2010-01-26 00:00:00.000'

DECLARE @ENDDATED DATETIME = '2010-02-25 23:59:59.999'

SELECT DATEDIFF(dd, @STARTDATED, @ENDDATED)

As you can see you ignored the 2010-02-25 at all!!!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 3

Um... no, Dugi. Don't use the 23:59:59.999 thing.

There are two reasons. First, anything larger than 23:59:59.997 will round up to the next day. Second, with some of the new datatypes for dates, you could miss data.

Always use "whole" days and if you use them in a WHERE clause, you should use something like the following to guarantee you miss no information but don't include accidental information due to rounding.

SELECT whatever

FROM sometablename

WHERE somedatecolumn >= @StartDate

AND somedatecolumn < DATEADD(dd,1,@EndDate)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

gaurav-404321 (3/30/2010)
I am using datediff functionm to calculate the days span between two dates
SET @StartDate=cast('2010-01-26 00:00:00.000' as datetime)
SET @EndDate=cast('2010-02-25 00:00:00.000' as datetime)
SET @TotalDaysInSalaryPeriod= DATEDIFF(dd,@StartDate,@EndDate)
this is giving 30 as output.
But this should be 31. Am i making some mistake?
Whats the behavior of datediff function? is it excluding startdate or end date or both are including

If you have items number from 0 to 9, how many items do you have? If you just do the subtraction, you end up with the wrong answer because there are 10 items. Count them... 0,1,2,3,4,5,6,7,8,9

The same holds true for dates. In order to get the correct number of days, you do the subtraction (DATEDIFF in this case) and then add 1.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dugi SSCoach Points: 17998 More actions · Answer 5

Jeff Moden (3/30/2010)
Um... no, Dugi. Don't use the 23:59:59.999 thing.
There are two reasons. First, anything larger than 23:59:59.997 will round up to the next day. Second, with some of the new datatypes for dates, you could miss data.
Always use "whole" days and if you use them in a WHERE clause, you should use something like the following to guarantee you miss no information but don't include accidental information due to rounding.
SELECT whatever
FROM sometablename
WHERE somedatecolumn >= @StartDate
AND somedatecolumn < DATEADD(dd,1,@EndDate)

Thank you for correction, I agree with you Jeff! But when I see the parameters with portion of the time a just make fast reply, I can see that the correct way to do the best is to add 1 day at the @EndDate, being sure which date you are using!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 6

I know, Dugi, and I appreciate your efforts. The problem is that not just the OP will read such a thing. Someone with similar but slightly different requirements may read from the thread and get it dreadfully wrong... especially on the .999 rounding problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 7

Jeff Moden (3/30/2010)
I know, Dugi, and I appreciate your efforts. The problem is that not just the OP will read such a thing. Someone with similar but slightly different requirements may read from the thread and get it dreadfully wrong... especially on the .999 rounding problem.

Yeah, like me... LOL

😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg