View Suresh Vaishya's profile
Below is an example of how to display dates into week Range in Oracle
For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08
Firstly lets see how to get week of the year. Following query can be used to get this
SELECT to_char(sysdate,'WW') FROM Dual;
Now lets get the week range
SELECT TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
|| ' to '
|| TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR')
FROM Dual;
The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08
3 Comments:
Hi Suresh,
I have a requirement that i need to pass the week as a parameter to the report and the report should fetch records for that week date range.
Please help me on this.
Thanks,
Uday
Uday,
Use following query to create list of values for week range and use it in your value set.
SELECT distinct TO_date (TRUNC (SYSDATE + level, 'IYYY') + ((TO_CHAR (SYSDATE + level, 'WW') - 1) * 7), 'DD-MON-RR'),
TO_date (TRUNC (SYSDATE + level, 'IYYY') + ((TO_CHAR (SYSDATE + level, 'WW') - 1) * 7), 'DD-MON-RR')
|| ' to '
|| TO_date (TRUNC (SYSDATE + level, 'IYYY') + ((TO_CHAR (SYSDATE + level, 'WW')) * 7)-1, 'DD-MON-RR')
FROM Dual
CONNECT BY LEVEL <= 500
order by 1
Thanks,
Suresh
hello sir i need to convert the single single date into a week like
the year was 2012 the entry start with the date 1-1-2012,2-1-2012,and so on .. this i have t0 convert as week like 1-1-1012 to 7-1-2012
Post a Comment