| Author |
 Topic  |
|
|
seeker62
Starting Member
34 Posts |
 Posted - 12/04/2012 : 14:10:38
|
| Access had a function that if a date was passed to it then it would give what week that date fell in. I would like to do have a date and then have the function tell me what week it is in. To further explain: date in one column of table is 11/1/12 and so i want another column to read 10/29/12 telling me that the november date falls in the week that starts with monday 10/29. |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/04/2012 : 14:26:10
|
SELECT DATEADD(week,datediff(week,0,'20121210'),0)
jim
Everyday I learn something that somebody else already knew |
 |
|
|
seeker62
Starting Member
34 Posts |
Posted - 12/04/2012 : 15:54:00
|
| Did the trick thanks |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/04/2012 : 17:28:56
|
You're welcome.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
seeker62
Starting Member
34 Posts |
Posted - 12/05/2012 : 09:36:03
|
ok we are just about there. How would the above code be tweeked to make sure that the Sunday date has previous week of not next week of.
If criteria date is 12/9 then the week of column needs to have 12/3 not 12/10. Our people define a week as Monday -- Sunday not Sunday -- Saturday |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/05/2012 : 10:23:35
|
DATEADD(dd,DATEDIFF(dd,0,'20121210')/7*7,0) |
|
|
|
seeker62
Starting Member
34 Posts |
Posted - 12/05/2012 : 10:29:57
|
| Thanks again. That worked. Now to understand the logic behind this. divide the datediff by 49 ??? HMMMMMMM |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 12/05/2012 : 10:46:58
|
This is from Michael Valentine Jones, he's done some awesome date stuff that you can search this ste for
select dateadd(dd,(datediff(dd,-53683,'20121209')/7)*7,-53683)
, dateadd(dd,(datediff(dd,-53683,'20121210')/7)*7,-53683)
Jim
Everyday I learn something that somebody else already knew |
Edited by - jimf on 12/05/2012 10:47:36 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/05/2012 : 12:15:26
|
quote:
Originally posted by seeker62
Thanks again. That worked. Now to understand the logic behind this. divide the datediff by 49 ??? HMMMMMMM
It is really integer division by 7 followed by multiplying the result by 7 - because the operator precedence (division and multiplication having the same precedence) causes the division to be done first and the result to be then multiplied by 7.
DATEADD(dd,(DATEDIFF(dd,0,'20121210')/7)*7,0)
This effectively results in rounding down to the nearest multiple of 7 that is less or equal to the value. |
|
|
| |
Topic |
|
week of
