CODEWITHSUNDEEP
plsqlgroup-by
breq
breq

Reputation: 25516

PL/SQL group by week

In MySQL I am using 

week(date,3)

to get correct values of week grouping.

How to translate this to PL/SQL? I'v tried the following but what about this 3 from week function in mysql? 

TRUNC (created_dt, 'IW')

Upvotes: 1

Views: 1143

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

Based on your MYSQL statment above, it returns the week of the specified date e.g. 2012-12-07, 3 as the second argument defines that the third day of the week is assumed as Monday...

If you look at this article it says there are 8 ways MYSQL WEEK() function can behave. So you gotta let us know what results you are trying to achieve by looking for MYSQL Week equivalent function in PL/SQL.

In most staright forward manner, MYSQL WEEK(date[mode]) returns the week number for a given date.

From re-reading your question, the only thing I grabbed that you want to achieve the first feature within PL/SQL and so you are looking for an equivalent function.

And with Oracle it gets slightly RAMEN...

  • W Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. It goes on with how the year starts. E.g. 2012 started on a Sunday, ORACLE THINKS.............. that weeks are Sundays to Saturday.
  • iw Week of year (1-52, 1-53) based on the ISO standard. Hence the weeks do not necessarily start on Sunday.
  • WW Week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

By default Oracle NLS settings are set to following:

  • NLS_CALENDAR : Gregorian
  • NLS_DATE_LANGUAGE: AMERICAN
  • NLS_TERRITORY: AMERICA

So you can suggest Oracle to follow the calendar by manipulating your query level....

You could something like this:

select trunc('2012-12-07','YY') AS week_number, 
       to_number(to_char(trunc('2012-12-07','YY')+rownum-1,'D')) AS dayNumber_in_week
        from dual connect by level <= 365
where  to_char('2012-12-07','IW') = 3
and    to_char('2012-12-07','DY') = 'MON';

Upvotes: 1

Peter &#197;
Peter &#197;

Reputation: 1319

Oracle is using the NLS setting of the database to determinate how the week number should be calculated and therefor there is no need (according to Oracle) for the '3' part pf the MySQL function. I can image that there still should be useful to have this option but this is once again a sign of the fact that Oracle does not fully understand the needs of working outside USA.

Upvotes: 1

Related Questions