TheSearcher
Registered User.
- Local time
- Today, 18:04
- Joined
- Jul 21, 2011
- Messages
- 358
Our business week starts on a Monday. I am using the following query to return the year, the week number, the date, the day of the week and the week start date:
The result is correct except for the TheWeekStart. How can I get it to recognize that the first day of the week is a Monday?
Thanks in advance,
TS
Code:
SELECT "2027" AS TheYear, DatePart("ww",[TheDate],2,2) AS TheWeekNo,
t_Calendar.TheDate,
t_Calendar.TheDay, DateAdd("d",-Weekday([theDate])+2,[theDate]) AS TheWeekStart
FROM t_Calendar;
The result is correct except for the TheWeekStart. How can I get it to recognize that the first day of the week is a Monday?
TheYear | TheWeekNo | TheDate | TheDay | TheWeekStart |
2027 | 1 | 1/4/2027 | Monday | 1/4/2027 |
2027 | 1 | 1/5/2027 | Tuesday | 1/4/2027 |
2027 | 1 | 1/6/2027 | Wednesday | 1/4/2027 |
2027 | 1 | 1/7/2027 | Thursday | 1/4/2027 |
2027 | 1 | 1/8/2027 | Friday | 1/4/2027 |
2027 | 1 | 1/9/2027 | Saturday | 1/4/2027 |
2027 | 1 | 1/10/2027 | Sunday | 1/11/2027 - THIS IS WRONG |
2027 | 2 | 1/11/2027 | Monday | 1/11/2027 |
Thanks in advance,
TS