Friday, March 9, 2012

Oracle PL/SQL Date Function

Hi,
Can someone tell me how to write a date function for the following:
AsOfDate = If Monday, current date - 3
Else current date - 1
The date needs to be displayed in mm/dd/yyyy format.
Any help is greatly appreciated.Using DECODE:

DECODE( TO_CHAR(SYSDATE,'DY'), 'MON', SYSDATE-3, SYSDATE-1 )

Using CASE:

CASE WHEN TO_CHAR(SYSDATE,'DY')='MON' THEN SYSDATE-3 ELSE SYSDATE-1 END

In either case the result is of type DATE: use TO_CHAR to convert to required format when displaying.

No comments:

Post a Comment