Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006

    Unanswered: Adding the result of an expression to date


    The SQL Server DATEADD function can be simulated in simulated as shown below

    SQL Server : SELECT DATEADD(day, 21, '11-JAN-2007');

    PostgreSQL : SELECT timestamp '11-JAN-2007' + interval '21 day';

    I have a particular scenario where the number of days to be added is no known beforehand
    and is calculated using certain functions as shown below :

    SELECT DATEADD(day,12-DATEPART(dw,'11-JAN-2007'),'11-JAN-2007') ;
    SELECT DATEADD(day,12-DATEPART(dw,order_date),delivery_date) from orders;

    Now i am not able to provide these function expressions in the interval option of PostgreSQL, it accepts only integers .
    The interval '21 day' does not accept functions in place of the integer value.

    Is there a way to cast the result of a function as a day and then add it to date.

    I want to do this in a single select query instead of using a stored procedure.

    thanks in advance.

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    the following expression returns all records from a calendar table for the month of February, 2005

    select * from tbl_cal
    where fld_date between '2/1/2005' and timestamp '2/1/2005' + abs(4 * 7) * interval '1 day'
    "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

Posting Permissions

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