Re: Translate week number to date of the week.
From: ddf <oratune_at_msn.com>
Date: Wed, 26 Dec 2012 14:12:18 -0800 (PST)
Message-ID: <ddd8b0d8-9c5c-4c86-bec2-1ed6e0d955af_at_googlegroups.com>
On Wednesday, December 26, 2012 10:47:24 AM UTC-7, jmmna..._at_gmail.com wrote:
> When I use wk_of_yr = 1;
>
>
>
> with date_wk
>
> as (
>
> select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
>
> from dual
>
> connect by level <= 366
>
> ),
>
> wk_dt as (
>
>
>
> select dt,
>
> to_number(to_char(dt, 'iw')) wk_of_yr
>
> from date_wk
>
> )
>
> select min(dt)
>
> from wk_dt
>
> where wk_of_yr = 1; -- the first ISO week of year 2012
>
>
>
> The value return is : 2012-01-02 00:00:00 (Wrong)
>
> The correct value should be : 2012-01-03 00:00:00 (Right)
>
>
>
> What I doing Wrong?
Date: Wed, 26 Dec 2012 14:12:18 -0800 (PST)
Message-ID: <ddd8b0d8-9c5c-4c86-bec2-1ed6e0d955af_at_googlegroups.com>
On Wednesday, December 26, 2012 10:47:24 AM UTC-7, jmmna..._at_gmail.com wrote:
> When I use wk_of_yr = 1;
>
>
>
> with date_wk
>
> as (
>
> select to_date('01/01/'||to_char(sysdate, 'RRRR'), 'MM/DD/RRRR') + rownum - 4 dt
>
> from dual
>
> connect by level <= 366
>
> ),
>
> wk_dt as (
>
>
>
> select dt,
>
> to_number(to_char(dt, 'iw')) wk_of_yr
>
> from date_wk
>
> )
>
> select min(dt)
>
> from wk_dt
>
> where wk_of_yr = 1; -- the first ISO week of year 2012
>
>
>
> The value return is : 2012-01-02 00:00:00 (Wrong)
>
> The correct value should be : 2012-01-03 00:00:00 (Right)
>
>
>
> What I doing Wrong?
Nothing except using the ISO definition for a week rather than the US definition. You should read here:
http://dfitzjarrell.wordpress.com/2009/01/28/that-was-the-week-that-wasnt/
David Fitzjarrell Received on Wed Dec 26 2012 - 23:12:18 CET