Thread: Re: Adding an INTERVAL to a variable
>>>>> "GC" == Graham Coates <graham@mindvision.com.au> writes: GC> SELECT Invoices.InvoiceDate + INTERVAL '41 Days' GC> works fine GC> but when trying to substitute the number of days with a value form a field GC> e.g. GC> SELECT Invoices.InvoiceDate + INTERVAL Acct.AverageDaysToPay 'Days' try SELECT Invoices.InvoiceDate + Acct.AverageDaysToPay::interval -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
Graham, > GC> SELECT Invoices.InvoiceDate + INTERVAL Acct.AverageDaysToPay > 'Days' Actually, all you're missing is some punctuation. Don't skimp on the :: and () ! Plus, you should use explicit CASTs wherever you remember them: SELECT Invoices.InvoiceDate + INTERVAL(CAST(Acct.AverageDaysToPay AS VARCHAR) || ' days'); Will work fine. Here I've explicitly cast the Average Days integer (if it's NUMERIC or FLOAT, you will have to use TO_CHAR()) to varchar, then concatinated it with the word "days". *Then* I convert it to INTERVAL, which will recognize '# days' as a valid expression. IMHO, you've been lucky being able to skip the parens and CASTs so far; get used to using them. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Attachment
Thanks for the inspiration Vivek! Didn't quite work, but when I made it... dSalesHdrDateDue + (iAcctPayDaysAv || 'Days')::INTERVAL BINGO! it works. Happiness is :-) Graham Coates "Vivek Khera" <khera@kcilink.com> wrote in message news:x73d737w54.fsf@onceler.kciLink.com... > >>>>> "GC" == Graham Coates <graham@mindvision.com.au> writes: > > GC> SELECT Invoices.InvoiceDate + INTERVAL '41 Days' > GC> works fine > GC> but when trying to substitute the number of days with a value form a field > GC> e.g. > > GC> SELECT Invoices.InvoiceDate + INTERVAL Acct.AverageDaysToPay 'Days' > > try > > SELECT Invoices.InvoiceDate + Acct.AverageDaysToPay::interval > > > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Vivek Khera, Ph.D. Khera Communications, Inc. > Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 > AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/