Oracle uses the to_char and next_day functions to obtain the first and last day of the week.

Source: Internet
Author: User

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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.