Menu
For my project I need to have an absolute numerical correspondence between days of the week and 1...7 values.
As you probably know the association between days and numbers can vary according to the locale, for example in Germany Monday is 1 and Sunday is 7, while in US Monday is 2 while Sunday is 1.
So, searching for a solution, I found the following code which seems working regardless of the locale, assigning Monday=1...Sunday=7:
1 + TRUNC (date) - TRUNC (date, 'IW')
Can someone explain me how does it work? In particular I just can't understand what this instruction:
TRUNC (date, 'IW')
exactly does.
779
The TRUNC (date) function is used to get the date with the time portion of the day truncated to a specific unit of measure. It operates according to the rules of the Gregorian calendar.
The TRUNC function replaces the value of its first argument with another value that has a smaller precision or the same precision. The TRUNCATE statement deletes all of the rows from a database table, without dropping the table schema.
The 'WW' function returns the week number based on 1-7 days (one being the first week of the year). Therefore the first seven days of the year will be regarded as the first week. There is another function named 'IW', which is based on a Saturday to Sunday days format. For example, January 6, 1998 is a Tuesday.
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
TRUNC(DATE,'IW') returns the first day of the week. For me TRUNC(SYSDATE,'IW) returns Monday. Today is Tuesday Feb 21. Subtract from that TRUNC(SYSDATE,'IW') which would be Monday the 20th, and you'll get 1 (because 21-20=1). Add 1 onto that as you do in the beginning of your equation and you get 2, which we associate with Tuesday.
65
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
