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

Thanks
SqlServer.png
dbo.qry-TopLvSchedDate---WeekNum.xlsx
BajanPaulAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
This is not an easy one, as you request the ISO 8601 week number.
Here is a function that will do:

dbo.udf_GetISOWeekdayNumberFromDate

/gustav
0
 
GRayLCommented:
What do you mean by 'the correct week'?  What format are you looking for in a 'correct week'?
0
 
BajanPaulAuthor Commented:
I want the dates to line up with the correct week.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GRayLCommented:
In Access it would be:

SELECT TopLvSchDate, Format(TopLvSchDate,"ww") AS [Week] FROM AllOrders ORDER BY TopLvSchDate;
0
 
BajanPaulAuthor Commented:
ADO error: Format is not a recognized function name
0
 
BajanPaulAuthor Commented:
Does that work with SQL server express 2005?
0
 
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
http://www.calendar-365.com/2011-calendar.html
0
 
GRayLCommented:
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
ORDER BY TopLvSchDate
0
 
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
ORDER BY TopLvSchDate
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
0
 
GRayLCommented:
Are all the other weeks correct?
0
 
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.

0
 
GRayLCommented:
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.  
0
 
BajanPaulAuthor Commented:
Thanks for the link.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.