If you have a NUMBER value p_YEAR, you can calculate the weekday of the 1. Select to_char ( date'' + level, 'day' ) dy, Whether you add or subtract depends on which day you consider to be the start of the fiscal year: You can convert these to Saturday - Friday weeks by either adding 2 or subtracting 5 from the date, then converting to the ISO week. How can the week start with Saturday and end on Sunday?! That's either a 2 day or 8 day week! (SELECT MAX(C_ED.EFFDT) FROM PS_SCH_DEFN_TBL C_EDĪND B.DAYNUM = (MOD((TO_DATE( A.START_DT,'YYYY-MM-DD') - TO_DATE( C.EFFDT,'YYYY-MM-DD')), C.SCHEDULE_DAYS))+1ĪND TO_CHAR(TO_DATE( A.START_DT,'YYYY-MM-DD'),'YYYY') = :1 ) (SELECT MAX(B_ED.EFFDT) FROM PS_SCH_DEFN_DTL B_ED TO_CHAR(TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'WW') + 0)ĮND, TO_CHAR(TO_DATE( TO_CHAR(A.START_DT,'YYYY-MM-DD'),'YYYY-MM-DD'),'d'), TO_CHAR(A.START_DT,'YYYY-MM-DD'), B.SCHEDULE_ID, B.SCHED_HRSįROM PS_TL_CALENDAR A, PS_SCH_DEFN_DTL B, PS_SCH_DEFN_TBL C I can then drop the results into a BI Pub and out comes a calendar grouped by month then week and I can see how many hours employees are scheduled for across a calendar year. Select to_char(dt, 'YYYY-MM-DD dy "IYYY"=IYYY "IW="IW "WW="WW') output WW starts January 1st, but not following Mon-Sun weeks or Sun-Sat weeks, but weeks using WW shift on the day that is January 1st, so using WW in 2016 makes weeks go from Fri-Thur.įinding the week number according to US standard - well as far as I know such a format model element does not exist? Or maybe I have just never encountered it? Then the first week of 2016 begins Monday. In ISO standard "Week 53-2015" goes from Monday to Sunday.
0 Comments
Leave a Reply. |