Computing Dates in SQL | ê¿êspi20.com -- putting it all into perspective

Dates, Dates and More Dates

Posted By ê¿ê on June 5th, 2008

My latest project at work has to do with dates. My head is swimming with dates!

It’s bad enough that dates are hard to deal with programmatically in SQL, but users being users, have an irritating habit of screwing up the dates they put in the database. They MEAN to type a 5, but hit the 8 instead and that throws your calculations off 3 minutes, 3 hours, 3 days, 3 months or 3 years depending on which 5 they mistyped!

My head hurts from trying to figure out how to deal with all these dates!

To compound my task, there are “only” 8 different events for which we track dates for and depending upon which event is under scrutiny, your calculation needs to be dynamic in order to deal with all these events.

I feel like I need a new brain!

This is some of the code I’m trying to deal with:

update #tt set trv=case when er<arv1 then datediff(mi, er, arv1) else 0 end
lab=case when arv1<>’1900-01-01 00:00:000′ and inc<>’1900-01-01 00:00:000′ and arv2<>’1900-01-01 00:00:000′ and co<>’1900-01-01 00:00:000′
then datediff(mi, arv1, inc)+datediff mi(arv2, co)
when arv1<>’1900-01-01 00:00:000′ and inc=’1900-01-01 00:00:000′ and arv2=’1900-01-01 00:00:000′ and co<>’1900-01-01 00:00:000′ then
datediff(mi, arv1, co)
when arv1=’1900-01-01 00:00:000′ and inc<>’1900-01-01 00:00:000′ and inc<=arv2 then
datediff(mi, arv2, co)
when arv1=’1900-01-01 00:00:000′ and inc=’1900-01-01 00:00:000′ and inc2<>’1900-01-01 00:00:000′ then
datediff(mi, arv2, co)

Here’s an example of my data:

callnbr techid er arv1 inc arv2 co

0000090902 10161 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 2008-05-03 01:50:30.000 2008-05-08 07:48:09.000 2008-05-08 07:48:23.000 0000091279 10423 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 2008-05-06 06:30:53.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 0000091780 10441 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 2008-05-06 15:37:56.000 2008-05-07 16:18:05.000 2008-05-07 16:29:14.000

What that code above is supposed to be doing is if the er is a real date (1900-01-01 00:00:00.000 is an empty date), then I figure how many minutes it took for the person to travel to the job by subtracting er from arv1 which is the time that marks his arrival at the site.

Then I need to record how long he was at the site and this should be a cumulative number. Sometimes he has to make more than one trip, so that’s why we have arv1 and arv2. If he made two trips, there SHOULD be a value in the inc column as well as in the arv2 column, so the time on site would be the difference between when he completed the job co and when he arrived arv1 or co-arv1 if inc has a real date in it, then inc-arv1+co-arv2.

Or he’s made one trip in the date range, but didn’t finish the job — I’m supposed to have an arrv that’s the same date as the inc, but you see here that I don’t. Guess he didn’t enter it on the computer.

And then there are instances where I don’t have an arv1 because he did that outside the date range I chose, but he has an inc date and no other dates within the date range. I’m not totally sure what I’m supposed to do with that information!

Now do you see why I’m pulling my hair out?

The now close to bald ê¿ê