Get Previous Day (ie Last Sunday)

From: ct witter <norcold1_at_yahoo.com>
Date: 18 Nov 2005 09:33:22 -0800
Message-ID: <1132335202.776008.32240@z14g2000cwz.googlegroups.com>

I'm trying to write a statement in SQL to return the date of the last sunday. ie today is
'18-NOV-2005', previous sunday was '13-NOV-2005'. I don't want to use a function because I don't have access to create functions.

I was thinking something along the lines of: ID.INSERT_DATE = SYSDATE - DATEPART(weekday, SYSDATE) + 1

but this didn't work. Below is my full select.

SELECT

IH.ORDER_NUMBER
ID.LINE_NUMBER
ID.NET_AMT

FROM
DB.INVOICE_HEADER IH,
DB.IVOICE_DETAIL ID
WHERE
IH.ORDER_ID= ID.ORDER_ID AND
ID.INSERT_DATE = SYSDATE - DATEPART(weekday, SYSDATE) + 1

Thanks,
CT Received on Fri Nov 18 2005 - 11:33:22 CST