Menu
I need to get the first day and the last day of the week from some strings that have the format like this:
'201118'
where 2011 is the year and 18 is the number of the week. Knowing the number of the week, how do I get the first and the last day of the week?
How do I do this?
865
Add the number of weeks which is the difference between the last obtained date's week number and the specified week number, and that will yield the last day of the desired week. Subtract 6 days to obtain the first day.
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.
Here's what I got to work: SELECT TRUNC(SYSDATE, 'MM') + LEVEL - 1 AS day FROM dual CONNECT BY TRUNC(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'MM') = TRUNC(SYSDATE, 'MM') ; The key in this query is TRUNC(SYSDATE, 'MONTH') which is the first day of the current month.
WEEK
select TRUNC(sysdate, 'iw') AS iso_week_start_date,
TRUNC(sysdate, 'iw') + 7 - 1/86400 AS iso_week_end_date
from dual;
MONTH
select
TRUNC (sysdate, 'mm') AS month_start_date,
LAST_DAY (TRUNC (sysdate, 'mm')) + 1 - 1/86400 AS month_end_date
from dual;
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
