Ms Access 2007 ADP, USING SQL SERVER to convert date to week

To all Ms Access and SQL server2005 express gurus,
I need to be able to convert dates to weeks.  I have tried to use { fn WEEK(TopLvSchDate) } and it will display weeks from 0-53...Is there a way to get the correct week based upon a date?

Please help if possible

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What do you mean by 'the correct week'?  What format are you looking for in a 'correct week'?
BajanPaulAuthor Commented:
I want the dates to line up with the correct week.
In Access it would be:

SELECT TopLvSchDate, Format(TopLvSchDate,"ww") AS [Week] FROM AllOrders ORDER BY TopLvSchDate;
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

BajanPaulAuthor Commented:
ADO error: Format is not a recognized function name
BajanPaulAuthor Commented:
Does that work with SQL server express 2005?
BajanPaulAuthor Commented:
I am looking at a few of my dates in December and I believe they should be in week 52.  I was hoping to get the function or calculation to ensure my dates/time fall coincide with the correct week.

27-Dec-11      53
28-Dec-11      53
29-Dec-11      53
What I provided was an Access solution.  I see now from re-reading the question you are using an Access 2007 ADP running with a SQL Server table.  Given your solution provides the numbers 0-53 representing the week of the year for a given date, what are you expecting?  The xlsx file shows that.  Are you saying you need the SQL Server query to provide the recordset as shown in the xlsx file? If so:

SELECT TopLvSchDate, DatePart(wk, TopLvSchDate) AS Week FROM [ccc\sarjeantpk].AllOrders
BajanPaulAuthor Commented:
what I have currently that I have tried to use is { fn WEEK(TopLvSchDate) } .  The attached excel sheet is the data set I exported out of access.  i just want my dates to line up with the corresponding week.

I just used your
SELECT TopLvSchDate, DatePart(wk, TopLvSchDate) AS Week FROM [ccc\sarjeantpk].AllOrders
and it is providing me the same data set.  
I want these dates in week 52, not 53.
27-Dec-11      53
28-Dec-11      53
29-Dec-11      53
Are all the other weeks correct?
BajanPaulAuthor Commented:
To be honest, I have not matched the days with the weeks to see if they do.  I will do so shortly and post.

Gustav BrockCIOCommented:
This is not an easy one, as you request the ISO 8601 week number.
Here is a function that will do:



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you say that #2011-01-01# falls in week 1, then a simple count will show you that 25-31 Dec 2011 will fall in week 53.  If you say that four or more days of Jan will constitute week 1, then 1 Jan 2011 will fall in week 52.  You will get this problem when 1 Jan fall on a Saturday in a normal year or on a Friday in a leap year - assuming your first day of the week is Sunday.   IOW, the system is not making a mistake.  
BajanPaulAuthor Commented:
Thanks for the link.
Gustav BrockCIOCommented:
You are welcome!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.