## You are here

# Some Informix DATETIME/INTERVAL Tips

## Determine the DATE of the first day of the current week.

```
(SELECT TODAY - (WEEKDAY(TODAY)) UNITS DAY FROM systables WHERE tabid=1)
```

Informix always treats Sunday as day **0** of the week. The **WEEKDAY** function returns the number of the day of the week as a value of **0 - 6** so subtracting the weekday from current day (**TODAY**) returns the **DATE** value of **Sunday** of the current week.

## Determining HOURS between two DATETIME values.

It is all about the **INTERVAL** data type and its rather odd syntax.

```
SELECT mpr.person_id, mpr.cn_name,
((SUM(out_time - in_time))::INTERVAL HOUR(9) TO HOUR) AS hours
FROM service_time_card stc
INNER JOIN morrisonpersonr mpr ON (mpr.person_id = stc.technician_id)
WHERE mpr.person_id IN (SELECT person_id FROM branch_membership WHERE branch_code = 'TSC')
AND in_time > (SELECT TODAY - (WEEKDAY(TODAY)) UNITS DAY FROM systables WHERE tabid=1)
GROUP BY 1,2
```

The "**(9)**" part of the expression **INTERVAL HOUR(9) TO HOUR** is key - it allocates lots of room for hours, otherwise any value of more than a trivial number of hours will cause the clearly correct by not helpful **SQL -1265 error**: "Overflow occurred on a datetime or interval operation". As, in my case I had a highest value of 6,483 hours I needed at least **HOUR(4) TO HOUR** to avoid the overflow error. **HOUR(9)** is the maximum - an expression of **HOUR(10)** results in an unhelpful generic **SQL -201**: "A syntax error has occurred.". On the other hand **HOURS(9)** is 114,155 years and some change, so... it is doubtful that is going to be a problem in most applications.

## Converting An INTERVAL To An Integer

I just want to know the number of hours! Seems so simple; but the INTERVAL data type is a frustrating beast, it has no *as units* operator and it has no useful casting rules [I mean, seriously, just default to seconds!]. You could descend into SPL land, write something to disect and accumulate the interval to hours or minutes... or you can *hack it* and convert the INTERVAL to a STRING - then cast the string to an INTEGER.

```
((SUM(out_time - in_time))::INTERVAL HOUR(9) TO HOUR)::VARCHAR(12)::INT AS hours
```

**Revolting!** But it works. Make sure your VARCHAR is long enough to contain your maximum value - which will includes a bit of white-space (just for kicks?). This would be so much cleaner if the engine had an INTERVAL->INTEGER casting rule.

- Log in to post comments