Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007

    Question Unanswered: Using character or numeric field for "date + interval"

    Hi everyone,

    I want to use character or numeric field for "date + interval" operation.

    aaa date field
    bbb numeric or character field

    select * from foo where aaa + interval bbb >= CURRENT_DATE

    how can i use numeric or character field for interval value?


  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    You'll need to CAST the data to an interval. In the case of a numeric though, you will need to explicitly add the time units for the interval.

    Just tried a little test.

    Created a table named dates. table dates contains 4 fields:

    table dates
    field name          field type
    start_date          date
    str_interval        varchar(20) default '6 Hours'
    int_interval        integer default 3
    interval_interval   interval default '5 days'
    I inserted a couple of records; one with the default interval values, one with different data.
    A little experimentation resulted in the following SQL (which worked just fine) :
    select 	start_date, str_interval, int_interval, interval_interval, 
    	start_date + cast(interval_interval as interval) as "NewDate 1",
    	start_date + cast(str_interval as interval) as "NewDate 2",
    	start_date + cast(cast (int_interval as text) || ' Hours' as interval) as "NewDate 3"
    from 	dates
    Last edited by loquin; 01-30-07 at 15:13.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Jan 2007

    Thanks for reply...

    Thanks for reply...

    I will tray...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts