October 3, 2016 at 6:20 am
Hello ,
I would like to find right date.
Example:
year = 2016
week = 40
day = 3 (tuesday)
How can I get date?
Thank you.
October 3, 2016 at 6:41 am
October 3, 2016 at 6:48 am
I dont need to define week 1. I know week, year, day and I need to get back date.
October 3, 2016 at 6:54 am
peter478 (10/3/2016)
I dont need to define week 1. I know week, year, day and I need to get back date.
The way you define week 1 will change the week number. So yes, you need to define what's week 1.
Is it the week where Jan-1 appears? Is it only if it has more than 4 days? Is it the first full week of the year?
Depending on that, we could be on week 41 or 40. Or maybe it's a different calendar and you start on december 25. That would set the week a higher number.
October 3, 2016 at 7:13 am
Let me explain what I need.
Based on last day in month I found week number via function DatePart("ww", 30.11.2016) result is 40 or other. It does not matter.
The task is just to find another date if I know week number, year, day.
October 3, 2016 at 7:23 am
I know what you're trying to do. What you fail to understand is that to get the correct calculation, you need to be able to define how to calculate week numbers. If you don't know how the first week of the year is calculated, eventually the calculation to get a date from a week number might be wrong.
October 3, 2016 at 7:40 am
Sorry 🙂 , I just wanted to find some solution (example!) how to do it, I expected solution from your side, but you are always writing about first week.
Week number is defined by me.
October 3, 2016 at 7:54 am
peter478 (10/3/2016)
Sorry 🙂 , I just wanted to find some solution (example!) how to do it, I expected solution from your side, but you are always writing about first week.Week number is defined by me.
We understand that you have week number. What you are failing to understand is that we can't determine what the date is based on week being 40 because we don't know how to calculate it. Why can't you simply answer the question? We can't provide an example here because you haven't provided the details required to answer the question.
Think of an algebra problem. x + y = z. Where z = 42 solve for y.
Seems pretty simple right? However, there are multiple answers for y because we don't know the value for x. This is the same as your question here. Without a basis to start a calculation we can't determine how to calculate this. And of course in your example:
DatePart("ww", 30.11.2016) result is 40
That is NOT 40, it is 49. Luis and others around here are willing to help but have to provide all the information so that we are able to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2016 at 8:05 am
Response to your first questions:
1. I don't know how the first week of the year is calculated.
2. I understand that the result is 49
October 3, 2016 at 8:16 am
Since you only want an example, here's one that works on SQL Server.
If you want to make it work on Access, you have to find the correct function names and syntax.
In SQL Server I could have done it much simple, but I'm not sure that it would work on Access.
You could also build a calendar table that includes columns for week number and week day.
DECLARE @Year int = 2016,
@Week int = 40,
@Day int = 3;
SELECT DATEADD( DD, @Day - 1, DATEADD( WW, DATEDIFF(WW, DATEFROMPARTS(2006,1,1), DATEFROMPARTS(@Year,1,1)) + @Week - 1, DATEFROMPARTS(2006,1,1)));
October 3, 2016 at 8:33 am
Thank you Luis, I would need something on Access, some tips how to do it, I hope I will find something.
October 3, 2016 at 8:37 am
peter478 (10/3/2016)
Thank you Luis, I would need something on Access, some tips how to do it, I hope I will find something.
Google the functions and change them to the Access functions. All have a correspondent function.
October 3, 2016 at 7:14 pm
This was removed by the editor as SPAM
October 4, 2016 at 4:43 am
Thank you very much !!!
October 4, 2016 at 3:38 pm
peter478 (10/3/2016)
Let me explain what I need.Based on last day in month I found week number via function DatePart("ww", 30.11.2016) result is 40 or other. It does not matter.
The task is just to find another date if I know week number, year, day.
Just to make a note here :: In SQL SERVER:
SET DATEFIRST 7 /* makes Sunday the start of the week */
SELECT DatePart(ww, '20161002') /*results in 41 */
SET DATEFIRST 1 /* makes Monday the start of the week */
SELECT DatePart(ww, '20161002') /* The same date, results in 40 */
So the week you are in does depend on the how you configure the week start day.
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply