trying to get the date of the first and last day of a week, given an arb. date

Question

I was looking up a way to solve the following:

Display the Week (eg: 1-52), start and end date of that week and a count of something.

The week, was figured to be something like: to_Char(, 'WW')

but i dont know how to get the first and last date of those weeks.

How do i do that with SqlPlus?

Edit:

Given: a table that contains a column of dates.

yes you can. my coworker has me referencing it as SQLPLus but i just call it oracle to most. — Fallenreaper, Feb 21 '13 at 03:48
An answer to a closely related question, [How do I calculate the week number given a date?](http://stackoverflow.com/questions/274861/how-do-i-calculate-the-week-number-given-a-date/275024#275024), may help you with some of the theoretical background. In the interim, look up the ISO 8601 week number format specifier. The first and last day of the week are Monday and Sunday in the ISO 8601 calendar. If you get the 'day of the week' as a number D (in the range 1-7), then you can subtract (D-1) days from the date to get the first day and add 6 days to get the last day of the week. — Jonathan Leffler, Feb 21 '13 at 03:53

score 3 · Accepted Answer · edited Feb 21 '13 at 09:25

3

Try something like this:

SELECT to_char(date_col, 'iw') w, trunc(date_col, 'd') st, trunc(date_col, 'd')+6 et
FROM your_table

References:

edited Feb 21 '13 at 09:25

Andriy M

answered Feb 21 '13 at 07:23

A.B.Cade

score 1 · Answer 2 · answered Feb 21 '13 at 03:50

1

For the first date of the week, try next_day(dateColumn-8, 'Monday')

For the last date of the week, try next_day(dateColumn-1, 'Sunday')

answered Feb 21 '13 at 03:50

hd1

what is dt-8? Is dt the column – Fallenreaper Feb 21 '13 at 03:51
`dt` is the given arbitrary date — presumably from a table. `dt-8` is 8 days before the given date; `dt-1` is the day before. – Jonathan Leffler Feb 21 '13 at 03:54
The first one should probably be `dateColumn-7`. – Andriy M Feb 21 '13 at 09:27

2 Answers2