![]() The following case command returns the begin of the first week of year: The first week of a year (by ISO stabdard) is the week with the first Thursday of the year in it. 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. Start of the week should be Sunday 12:00 AM and end of week, Saturday 11:59 PM.In ISO standard "Week 53-2015" goes from Monday to Sunday. Since 08-NOV-09 10.00000 PM, is a Sunday, I would like to see a new WEEK_NUM associated to it. SELECT EVENT_TIME, to_char(EVENT_TIME, 'iw') WEEK_NUM NLS_RDBMS_VERSION-10.2.0.3.0Įxample of the issue I am trying to resolve: NLS_TIMESTAMP_TZ_FORMAT-DD-MON-RR HH.MI.SSXFF AM TZR NLS_TIMESTAMP_FORMAT-DD-MON-RR HH.MI.SSXFF AM Given below is my NLS_DATABASE_PARAMETERS: I would like to change the beginning of week in Oracle from Monday to Sunday. 1.7K Training / Learning / Certification.165.3K Java EE (Java Enterprise Edition).7.9K Oracle Database Express Edition (XE).3.8K Java and JavaScript in the Database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |