How to Calculate Mondays date for the current week

[cantona]

Im trying to produce a query that will give me Monday’s date for the current week. I can easily work out the next Monday’s date using Next_Day(sysdate,’Monday’) , I could then subtract 7 to get the required date. This however seems a bit long winded! Is there an easier way to calculate this?

 

[Dagon]

SELECT trunc(SYSDATE, 'DY')+1 from dual

 

[karluk]

Dagon's solution is short and elegant, but you should be aware that whether it works or not is dependent on the value of NLS_TERRITORY. There are a surprising number of countries around the world where Sunday isn't the first day of the week. If there is any chance that the Monday calculation will be executed with differing values of NLS_TERRITORY, you should probably stick with the "Next_Day(sysdate,’Monday’)-7" solution outlined in your original question.

We discussed this NLS_TERRITORY dependency in the Oracle 8i forum back in the days when our esteemed colleague, sem, was an active member of the forum. Sem was from Eastern Europe, so this NLS_TERRITORY issue was of more than just theoretical interest to him. See thread186-166342 for details.

 

[cantona]

Hi Dagon, Karluk,

Thanks for all your help. I tried SELECT trunc(SYSDATE, 'DY')+1 and this gave the wrong date. Im presuming my NLS Territory will be set to United Kingdom, with Monday as the first day of the week. Ive simply removed the +1 and it now works fine.