Tuesday, March 30, 2010

Query to calculate week number from a given date taking monday as first date of week in pl sql

SELECT CEIL ( ( 6
+ (TRUNC((TO_DATE('24-Jan-10','dd-Mon-yy')
+ DECODE(TRIM(TO_char(TO_DATE('24-Jan-10','dd-Mon-yy'),'DAY')),'MONDAY', +1,'SUNDAY',-1,0))
, 'd')
- TRUNC(TO_DATE('24-Jan-10','dd-Mon-yy'), 'Y')
)
)
/ 7
) from dual

No comments: