Author |
Topic |
seeker62
Starting Member
40 Posts |
Posted - 2012-12-04 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-04 : 14:26:10
|
SELECT DATEADD(week,datediff(week,0,'20121210'),0)jimEveryday I learn something that somebody else already knew |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2012-12-04 : 15:54:00
|
Did the trick thanks |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-04 : 17:28:56
|
You're welcome.JimEveryday I learn something that somebody else already knew |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2012-12-05 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-05 : 10:23:35
|
[code]DATEADD(dd,DATEDIFF(dd,0,'20121210')/7*7,0)[/code] |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2012-12-05 : 10:29:57
|
Thanks again. That worked. Now to understand the logic behind this. divide the datediff by 49 ??? HMMMMMMM |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-12-05 : 10:46:58
|
This is from Michael Valentine Jones, he's done some awesome date stuff that you can search this ste forselect dateadd(dd,(datediff(dd,-53683,'20121209')/7)*7,-53683) , dateadd(dd,(datediff(dd,-53683,'20121210')/7)*7,-53683)JimEveryday I learn something that somebody else already knew |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-05 : 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. |
|
|
|