-
Extract Fiscal Week
I have a table t1 with col1 with values DAYMONYear
col1
13012003
....
....
27032003
28032003
29032003
Question is how do I find out the fiscal week of the each row (Fiscal week 1 = Jan1-Jan7)
Thanks in Advance
-
I got it
select col1, to_char(to_date(col1, 'DD/MM/YYYY'), 'WW') from t1
Thanks
-
Best to store these dates in Data format.
Anyway, convert the numbers to dates ...
to_date(to_char(col1),'DDMMYYYY')
... then extract the day of the year ...
to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD')
... convert it to a number ...
to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))
...
then divide by seven, ignore remainder, and add 1...
floor(to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))/7)+1
... and that should be close.
-
Originally posted by slimdave
... and that should be close.
Hm, TO_CHAR(v_date, 'WW') should be even closer. And it's allready built in, and it's a hell lot shorter.....
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Originally posted by slimdave
Best to store these dates in Data format.
Anyway, convert the numbers to dates ...
to_date(to_char(col1),'DDMMYYYY')
... then extract the day of the year ...
to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD')
... convert it to a number ...
to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))
...
then divide by seven, ignore remainder, and add 1...
floor(to_number(to_char(to_date(to_char(col1),'DDMMYYYY'),'DDD'))/7)+1
... and that should be close.
I'm having flashbacks from Freshman Calculus...
Jeff Hunter
-
I think I'm having flashbacks to trying to calculate a client's nonsensical fiscal weeks. of course WW is easier.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|