BajanPaul
asked on
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
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
GRayL
What do you mean by 'the correct week'? What format are you looking for in a 'correct week'?
ASKER
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;
SELECT TopLvSchDate, Format(TopLvSchDate,"ww") AS [Week] FROM AllOrders ORDER BY TopLvSchDate;
ASKER
ADO error: Format is not a recognized function name
ASKER
Does that work with SQL server express 2005?
ASKER
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
27-Dec-11 53
28-Dec-11 53
29-Dec-11 53
http://www.calendar-365.com/2011-calendar.html
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
SELECT TopLvSchDate, DatePart(wk, TopLvSchDate) AS Week FROM [ccc\sarjeantpk].AllOrders
ORDER BY TopLvSchDate
ASKER
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
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
Are all the other weeks correct?
ASKER
To be honest, I have not matched the days with the weeks to see if they do. I will do so shortly and post.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
Thanks for the link.
You are welcome!
/gustav
/gustav