Larry, > contract_start date > contract_term int (term in MONTHS) > > I want to calculate the contract end date. I came up with: > CREATE FUNCTION "get_contract_end" (integer) RETURNS date AS 'SELECT > cast(contract_start + cast(cast(contract_term as text) || '' month'' > as > interval) as date) > FROM circuit > WHERE internal_id = $1;' LANGUAGE 'sql';
Not with those data types. Plus, there is an implicit conversion date --> timestamp --> date in the above, which can get you in trouble. You could use TIMESTAMP and INTERVAL instead: contract_start TIMESTAMP WITHOUT TIME ZONE contract_term INTERVAL select (contract_start + contract_term) as contract_end; Simple, neh? The only trick is on the end of saving the data. You have the user input an integer, then save (using RULES or your interface code): "interval"(cast($term as varchar) || ' months') This approach makes you do a little more work on the data entry end of things, but speeds up querying considerably. Also, should your company policy change in the future to permit contract terms in weeks or years, you will be ready to accomodate it. -Josh Berkus ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
