CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
uno
uno

Reputation: 99

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, when 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

Upvotes: 1

Views: 3157

Answers (3)

pratik garg
pratik garg

Reputation: 3342

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) :)

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36922

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'.

Upvotes: 1

bensiu
bensiu

Reputation: 25604

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

Upvotes: 0

Related Questions