Search This Blog

Saturday, November 22, 2008

Convert Date into Week Rage

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:

Udaa said...

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

Suresh Vaishya said...

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

Unknown said...

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

Copyright (c) All rights reserved. Presented by Suresh Vaishya