Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 week of

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)

jim

Everyday I learn something that somebody else already knew
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 2012-12-04 : 15:54:00
Did the trick thanks
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-04 : 17:28:56
You're welcome.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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
Go to Top of Page

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]
Go to Top of Page

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
Go to Top of Page

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 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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -