CODEWITHSUNDEEP
sqloracle-database
Abhijith
Abhijith

Reputation: 77

How to print last sunday of the year in Oracle

I need to print the last sunday of the year from which adding 7 to it will give me all the sundays of next year.

I have the code to print all sundays for a particular year if i have a start date but i need the user to put the year so that last sunday of the previous year will be generated and 7 will be added to get first sunday of that year and so on till it reaches last sunday of next year

For example input year is 2017 it will check the last sunday of 2016 and add 7 to it to get first sunday of 2017 which is 1-1-2017 and it will go on printing all sundays till it reaches 31st december 2017

Upvotes: 1

Views: 473

Answers (1)

user5683823
user5683823

Reputation:

The function next_day() takes two arguments: a date and the name of a day of the week. It returns the closest "next" day (following the date argument) that matches the given day of the week. So the result is between one and seven days forward. (If you want 'Tuesday' and the input date is a Tuesday, the function returns the date seven days later.)

If you want the last Sunday of a year, it will be between Dec. 25 and Dec. 31. So if you call the next_day() function with the arguments Dec. 24 (!!) and 'Sunday' you'll get what you want.

The result will have the same time-of-day as the date argument, so if you give a date without a time-of-day, so will be the output (which is probably what you want). So:

select next_day(date '2016-12-24', 'Sunday') from dual;

NEXT_DAY(D
----------
2016-12-25

Added: If you take an input from your user, as a bind variable, you can do something like this:

select next_day(to_date(:input_year - 1 || '-12-24', 'yyyy-mm-dd'), 'Sunday') from dual;

If you provide 2017 as input (whatever mechanism your interface has for bind variables), the output will be 2016-12-25 (in DATE data type, so don't ask "in what format" - dates don't have a format!)

Upvotes: 3

Related Questions