I was asked by a customer to create a scheduled weekly report detailing work that had been completed in the previous week (Monday to Friday) I figured they might lose the report or something might happen to stop the scheduler from running it, and I didn't want to have to modify my script to work out date ranges for the selection criteria if I ran the report later in the current week.  So the script below works with a variable date range based on a calculation of the current weekday. The dates are converted to a varchar(12) to truncate timestamps.

However, this is only good if you run the report on a weekday (Mon-Fri), it kinda  falls over if you run it on a weekend as it will include weekend dates which is easily fixed by filtering the dates to ignore weekdays 1 & 7.  But then, who works weekends?