SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 week of
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

seeker62
Starting Member

40 Posts

Posted - 12/04/2012 :  14:10:38  Show Profile  Reply with Quote
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
2870 Posts

Posted - 12/04/2012 :  14:26:10  Show Profile  Reply with Quote
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 - 12/04/2012 :  15:54:00  Show Profile  Reply with Quote
Did the trick thanks
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 12/04/2012 :  17:28:56  Show Profile  Reply with Quote
You're welcome.

Jim

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

seeker62
Starting Member

40 Posts

Posted - 12/05/2012 :  09:36:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/05/2012 :  10:23:35  Show Profile  Reply with Quote
DATEADD(dd,DATEDIFF(dd,0,'20121210')/7*7,0)
Go to Top of Page

seeker62
Starting Member

40 Posts

Posted - 12/05/2012 :  10:29:57  Show Profile  Reply with Quote
Thanks again. That worked. Now to understand the logic behind this. divide the datediff by 49 ??? HMMMMMMM
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 12/05/2012 :  10:46:58  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/05/2012 :  12:15:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000