To be accurate, a week starts from Sunday to Saturday, and our habits are that the first day of a week is Monday, and the last day is Sunday.
Many query conditions and statistics require a period of one week, that is, the period from Monday to Sunday. We can use the to_char and next_day functions to obtain the date of the first day and the last day respectively.
1. to_char is obtained by combining the decode and to_number functions.
/** Use to_char (sysdate, 'D') to obtain the day of the week when the current date is
The result is that Sunday starts as 1st days,
Monday is 2nd days and Sunday is 8th days.
*/
Select to_char (sysdate, 'yyyy-MM-DD ') today,
Decode (to_char (sysdate, 'd '),
'1', 'sunday ',
'2', 'monday ',
'3', 'tuesday ',
'4', 'weday ',
'5', 'thurs ',
'6', 'Friday ',
'7', 'saturday') day of the week,
To_char (sysdate-to_number (to_char (sysdate, 'D') + 2, 'yyyy-MM-DD ') Monday,
To_char (sysdate-to_number (to_char (sysdate, 'D') + 8, 'yyyy-MM-DD ') Sunday
From dual
2. next_day combined with the substr Function
/** Replace the decode function with substr
The next_day function specifies the date of the next week of the current date.
For example, if today is, Saturday, then next_day (sysdate, 'monday ')
The date is 11-27, and the date of this Monday is 11-27 minus 7 days.
The Sunday date is 11-27 minus 1 day.
*/
Select to_char (sysdate, 'yyyy-MM-DD ') today,
'Week' | substr ('february 5, ', to_number (to_char (sysdate, 'D'), 1) day of the week,
To_char (next_day (sysdate, 'monday')-7, 'yyyy-MM-DD ') Monday,
To_char (next_day (sysdate, 'monday')-1, 'yyyy-MM-DD ') Sunday
From dual