Calendar and Business Date Functions/Calculations

Question

Calendar and Business Date Functions/Calculations

Viewing 7 posts - 16 through 21 (of 21 total)

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

JacekO SSCertifiable Points: 6610 More actions · Answer 1

Jeff,

Just read your column and now I understand your reason for the extra day. And by the way I think you don't need the stripping of the time portion of the dates because the DATEDIFF takes care of it.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = '01/01/2007'

SET @EndDate = DATEADD(ms, -3, '01/03/2007')

SELECT DATEDIFF(day, @StartDate , @EndDate)

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Yeah, you're right... good catch and good eye ... nobody else caught that little boo-boo...

--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: 1004406 More actions · Answer 3

Haven't done any million row testing but the use of DATEPART may be faster because it uses integer math instead of string comparisons... I just didn't want to have to worry about DATEFIRST settings nor answer a bunch of questions about it in the article.

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

JacekO SSCertifiable Points: 6610 More actions · Answer 4

I do a lot of code inspections so it helps.

And for your second post. I often sacrifice a bit of performance (if I can) for code readability...

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Finally... someone else who believes in readability of code!

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

Using DATENAME eliminates dependency on the setting of DATEFIRST, but it introduces a dependency on the setting of LANGUAGE.

This code gives a day of week number that is independent of both. It gives 0 for Monday through 6 for Sunday. Basically, it just calculates the difference between 1753-01-01 and a.DATE, calculates the modulo of that result with a divisor of 7, producing a number from 0 through 6.

select
 *
from
 MyTable A
where
 -- Select only weekends
 datediff(dd,-53690,a.DATE)%7 > 4

It can also be coded like this for readability:

select
 *
from
 MyTable A
where
 -- Select only weekends
 datediff(dd,'17530101',a.DATE)%7 > 4

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Now we're cookin'... nicely done.

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