SQL - Week calculation

I've written an SQL statement which gets the current week based on sysdate. For eg: If the date is March 12th, 2011 (saturday) then the o/p should be

03/06/2011-03/12/2011

But somehow, whe开发者_C百科n I run the below SQL query, I get an o/p :

03/06/2011-03/13/2011

Can someone pls help me figure out what is wrong with the query? Thanks

SELECT CASE
         WHEN TO_CHAR(SYSDATE,'DAY') = 'SATURDAY' THEN 
          TO_CHAR(next_day(SYSDATE-7,'SUNDAY'),'mm/dd/yyyy') || '-' ||to_char(sysdate,'mm/dd/yyyy') 
         WHEN TO_CHAR(SYSDATE,'DAY') = 'SUNDAY' THEN
          TO_CHAR(SYSDATE,'mm/dd/yyyy') || '-' ||to_char(next_day(sysdate,'SATURDAY'),'mm/dd/yyyy') 
         ELSE 
          TO_CHAR(next_day(SYSDATE-7,'SUNDAY'),'mm/dd/yyyy') || '-' ||to_char(next_day(sysdate,'SUNDAY'),'mm/dd/yyyy') 
       END weeks 
  FROM DUAL

The 'DAY' format is right padded so that all results are the same length. (I don't know why Oracle does this, it seems silly to me.) You want to either look for 'SATURDAY ' and 'SUNDAY ', or trim the results, or use the format 'FMDAY'.

Just one change you have to do in your query..

SELECT CASE
WHEN TO_CHAR(SYSDATE,'DAY') = 'SATURDAY' THEN
        TO_CHAR(next_day(SYSDATE-7,'SUNDAY'),'mm/dd/yyyy') || '-' ||to_char(sysdate,'mm/dd/yyyy')
       WHEN TO_CHAR(SYSDATE,'DAY') = 'SUNDAY' THEN
       TO_CHAR(SYSDATE,'mm/dd/yyyy') || '-' ||to_char(next_day(sysdate,'SATURDAY'),'mm/dd/yyyy')
       ELSE
        TO_CHAR(next_day(SYSDATE-7,'SUNDAY'),'mm/dd/yyyy') || '-' ||

to_char(next_day(sysdate,'SUNDAY')

,'mm/dd/yyyy') END weeks FROM DUAL

in your this query in else part by mistake you are checking for sunday.

and you want to find out saturday as end of the week.

so just change this sunday to saturday then you will get your desired answer (result) :)

why you do not make your life easier with

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayname

or

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek