Yet another date FAQ

[vongrunt]

Just written... probably not finished: faq183-5842

Comments, suggestions, spelling checker outputs and kitchen sinks welcomed .

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[SQLBill]

Excellent. I like the tips and by creating the FAQ you put them all in one place.

-SQLBill

Posting advice: FAQ481-4875

 

[r937]

did you see my post the other day about calculating age?

instead of CASE it used SIGN(FLOOR(...

(

http://www.tek-tips.com/viewthread.cfm?qid=1051125

)

should be faster (would love one of you guys with the big test databases to try it for me)

in the same vein, your CONVERT solution for 4 is clunky, you should really just go with this --

dateadd(d,datediff(d,0,yourdate),0)

(

http://www.tek-tips.com/viewthread.cfm?qid=972119

)

which you do mention further down, but it needs more prominence





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[vongrunt]

instead of CASE it used SIGN(FLOOR(...

Simple test:

declare @dob smalldatetime; set @dob = '19700503' -- leap year
declare @today smalldatetime; set @today = '20050503' -- not a leap year

select datediff(yy,@dob,@today)
      - sign( floor(datepart(dy,@dob)/datepart(dy,@today)) )

Got 34, needed 35

... which you do mention further down, but it needs more prominence

I agree overall - will try to rearrange tips a little bit.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[r937]

oh. my. god.

i can't believe i forgot about leap days

you shoulda corrected me in that other thread, my friend

let me see if i can finagle the formula for leap days

after we discussed how integer arithmetic is always faster, i've been very leery of using CONVERT in date calulations...

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[ESquared]

Great FAQ. I'd like to see you add the difference between

DateDiff(s, Date1, Date2)

and

DateDiff(s, 0, Date1 - Date2)


Run the following code to see why this is important:

declare @d1 datetime, @d2 datetime
set @d1 = '8:00:00.997'
set @d2 = '8:00:01.000'

select datediff(s, @d1, @d2)
select datediff(s, 0, @d2 - @d1)
select @d2-@d1

See that the first method gives one second even though only 3 milliseconds have elapsed... only by the second method can you be sure that at least one whole second has elapsed. And if you wanted to have no more than one whole second elapse, again you must use the second method:

set @d1 = '8:00:00.000'
set @d2 = '8:00:01.001'

select datediff(s, @d1, @d2)
select datediff(s, 0, @d2 - @d1 + '0:00:00.997')
select @d2-@d1

But it occurs to me that even better is:

@d2 <= @d1 + '00:00:01'
(I form it this way to allow an index on @d2 to be used, if @d2 were a column, otherwise it would be @d2 - @d1 <= '00:00:01' )

So datediff in this case should be dispensed with entirely, for we are no longer counting boundary-crossings but looking for exact elapsed times.

Datediff may still be required if one is using variable-length time periods such as months or years. But for fixed-length time periods such as weeks, days, hours, minutes, and seconds, just do the math.

-------------------------------------
Only the unvirtuous can be dutiful, for the virtuous already perform duty's requirements by inclination and for pleasure. Where there is no pain, no disinclination, there is no duty.
- Erik E

 

[earthandfire]

Vongrunt, are you sure 1970 was a leap year?

 

[vongrunt]

I'd like to see you add the difference between...

Will do, though this is more fundamental thing (how DATEDIFF() works etc) than a tip or trick. Part 0?

Vongrunt, are you sure 1970 was a leap year?

No, it wasn't... where did you get that?

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[earthandfire]

I assumed that you were indicating that 1970 wasa leap year from:

declare @dob smalldatetime; set @dob = '19700503' -- leap year

Sorry if I misunderstood.

VB (6/.Net), VBA (Access), Delphi and some x86 assembler I'm ok with. SQL in Access I'm happy with, but I'm using this forum to help me with SQL Server's SQL.

Several of the contributors, such as yourself, James Lean, r937 and a few others appear to be very knowledgeable and very good at "laterally" thinking through a problem. Your IsLeapYear solution from the other day and James Lean using PARSENAME to split a floating point number at the decimal point are prime examples. Where I am unsure of the reason or logic behind your answers I've dared to query them, especially where I think that the answer may be wrong. My impression from -- leap year was that you were saying that 1970 was a leap year, and on the next line that 2005 wasn't.

 

[vongrunt]

No, it wasn't... where did you get that?

. PgUp/PgUp... there. You are correct.

My thought process was simple. DATEDIFF(dy) takes leap day into count. Age calculation doesn't. So any calculation based on DATEDIFF(dy) without a "patch" for leap years is not 100% safe. After that I stopped to analyze rudy's formula - and ignored the fact it always returns one year less at birthday. That said, fix is easy:

select datediff(yy,@dob,@today)
	- sign( floor([b]([/b]datepart(dy,@dob)[b]-1)[/b]/datepart(dy,@today)) )

Borderline situations when this formula still fails obviously must meet these conditions:

- both dates have the same month/day
- both fall after Feb 28
- @dob is in leap year, @today is not

Why 1970... this is probably one of these brain things. Originally I checked with '19720503' and got one year less as expected. At the same time I was about to finish rearranging FAQ (incl. UNIX/SQL2 tip, which contains Y1970 a lot), then for some weird reason picked '19700503' for second check and assumed it is a leap year, still got one year less - and posted reply based on results.

In other words: logic looks OK, 1970 is a typo.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba