Re: Interval data types and SQL Server

Brian,

> My task is to get the application... Coldfusion.... to work with both
> postgres and sql server 2000 with the same coldfusion code... this
> makes maintenance and ongoing development of the app easier because
> any changes can be exported to all the various sites running it...
> regardless of the backend database.

Coldfusion? Things just get worse and worse. I feel your pain.

> So my task is to decide how to store the interval... in a ??? char
> ??? field perhaps ??? and then process that with coldfusion for
> presentation... I know I've got a bit of work(understatement) ahead
> of me.

Well, SQL Server 2000 gives you the ability to create custom functions,
which should be your salvation.

1. Create your interval column as and integer and text, and save
interval values like so:
interval_number interval_type
3 months
15 hours
This destroys your ability to store compound intervals (like '3 months
12 days 8 hours') but it's a sacrifice you'll have to make.

2. Create a function in each database called "add_interval(datetime,
integer, text)"
In postgres, this function will be very simple:
CREATE FUNCTION add_interval ( timestamp, int4, text )
RETURNS timestamp AS '
SELECT ($1 + "interval"(($2 || '' '' || $3)));
' LANGUAGE 'sql';
In SQL Server 2000, you will have to use some if/then statements and
the various DATEADD() functions. But you can do it.
ORACLE should be as easy as Postgres.

3. Then, anywhere in your application that you normally would simply
add intervals, you can call add_interval and it will work on all three
database platforms.

Now, you owe me one for the advice. So, I demand that you write up
this solution to be posted at Techdocs once you've done it, with all
code.

-Josh Berkus