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.This is a somewhat rare instance, and it may look like there’s a problem with the logic, but because of how April of 2006 falls, the 30th would actually be in the 6th week of the month based on our criteria. Here we have specified that our week starts on Wednesday, and based on that February 6th will be in the first week in February. This shows that February 6 will be in the second week in February. Here we see that 1/29/06 was in the 5th week of January.
Remember, where we have not specified a starting day our function will assume Sunday. The function correctly identifies that we’re in week 1 of the month by our original definition. Now let’s try our function without parameters. If the week_start is not specified we’ll default to Sunday. If the check_date is not specified, the current date and time will be used. The function will return the week number from start of month. This must be spelled out, like ‘Sunday’, ‘Monday’, etc. The second parameter, week_start is the day that your week starts. The first, check_date can be any Oracle date. Here I’ve created a function week_in_month to execute this SQL with two parameters. Select trunc(((check_date - next_day(trunc(check_date, 'MM') - 7, week_start))/7),0) + 1 into week_number from dual (check_date DATE DEFAULT sysdate, week_start CHAR DEFAULT 'Sunday') It’s easiest to do that if we just create a function for it. Now we want to try this logic out with some other dates. This tells us accurately that right now we’re in the first week of the month. That would put us in the 0th week of the month, but since we want to start counting at 1 we add 1 to the number. We don’t care about the fractional part so we can use the other form of the TRUNC function to to truncate this to a whole number. SQL> select (sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday'))/7 from dual To express that in weeks we simply divide by 7. This tells us we are 3.559 days from the beginning of the first week in the month. SYSDATE-NEXT_DAY(TRUNC(SYSDATE,'MM')-7,'SUNDAY')
SQL> select sysdate - next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual With this date in mind we can now calculate how many days we are from then: The first week starts at the end of the previous month as it is only partially in February. Now we have established the beginning of the first week in the month.
SQL > select next_day(trunc(sysdate, 'MM') - 7, 'Sunday') from dual Since next_day excludes the day it is calculating from we subtract 7 for the start date. This is found by looking at the first of the month and the 6 days leading up to it. From here I used the NEXT_DAY function to find the beginning of the first week of the month. SQL> select trunc(sysdate, 'MM') from dual Today is February 1, so since we’re using sysdate for some testing we coincidentally get back February 1. The TRUNC function will allow you to truncate a date to a certain precision, so I started by truncating the date to the month.
Oracle week number how to#
Starting simple I worked out how to find the first day of the month. If you are unfamiliar with manipulating dates in Oracle, you may want to check out my other articles on Oracle, SQL, Dates and Timestamps and Performing Math on Oracle Dates. If the month happens to start on a Sunday week 1 would be a complete week. Ajama commented on my article Oracle Question and Answer Site asking how to write a SQL function to return the calendar week in a month.īasically if your week starts on a Sunday, anything leading up to the first Sunday would be week 1, then the first complete week, Sunday through Saturday would be week 2, then week 3, etc.
0 kommentar(er)
