How can I convert YYWW to date format based on day[Ex: Monday's date in given YYWW ] in SqlServer?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
1
I have a column in my table with YYWW format. I need to convert this YYWW and get Monday's date.
For Example:
Input YYWW: 1847
Expected Output: 2018-11-19 [Monday's date in 2018 Week 47]
Thanks in advance
I tried the below but does not work properly
declare @value int = 1519
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, 2000 + @value / 100-1900, 7 * (@value % 100)-7), 105);
1851 -Expected 17-12-2018[Monday] Works fine for this year 2018
1752 -Expected 25-12-2017[Monday] but shows 24-12-2017 [Sunday]
1652 -Expected 26-12-2016 [Monday] but shows 24-12-2016 [Saturday]
1519 -Expected 04-05-2015 [Monday] but shows 07-05-2015 [Thursday]
sql-server date format asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
|
show 2 more comments
1
I have a column in my table with YYWW format. I need to convert this YYWW and get Monday's date.
For Example:
Input YYWW: 1847
Expected Output: 2018-11-19 [Monday's date in 2018 Week 47]
Thanks in advance
I tried the below but does not work properly
declare @value int = 1519
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, 2000 + @value / 100-1900, 7 * (@value % 100)-7), 105);
1851 -Expected 17-12-2018[Monday] Works fine for this year 2018
1752 -Expected 25-12-2017[Monday] but shows 24-12-2017 [Sunday]
1652 -Expected 26-12-2016 [Monday] but shows 24-12-2016 [Saturday]
1519 -Expected 04-05-2015 [Monday] but shows 07-05-2015 [Thursday]
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
What attempts have you made so far? What is the lowest value that the first digits can have? Should be, for example, assume 7021 is 1970 or 2070?
– Larnu
Nov 22 '18 at 8:26
Year is always in between 2000 and 2100
– K SuryaPrakash
Nov 22 '18 at 8:27
Also, do you have a calendar table? Depending on where/who you are, the start of the week varies.
– Larnu
Nov 22 '18 at 8:27
Ok, we still need to see your attempt then please.
– Larnu
Nov 22 '18 at 8:28
Edited Post with my trials. Please have a look. Thanks!
– K SuryaPrakash
Nov 22 '18 at 8:43
|
show 2 more comments
1
1
1
I have a column in my table with YYWW format. I need to convert this YYWW and get Monday's date.
For Example:
Input YYWW: 1847
Expected Output: 2018-11-19 [Monday's date in 2018 Week 47]
Thanks in advance
I tried the below but does not work properly
declare @value int = 1519
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, 2000 + @value / 100-1900, 7 * (@value % 100)-7), 105);
1851 -Expected 17-12-2018[Monday] Works fine for this year 2018
1752 -Expected 25-12-2017[Monday] but shows 24-12-2017 [Sunday]
1652 -Expected 26-12-2016 [Monday] but shows 24-12-2016 [Saturday]
1519 -Expected 04-05-2015 [Monday] but shows 07-05-2015 [Thursday]
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
I have a column in my table with YYWW format. I need to convert this YYWW and get Monday's date.
For Example:
Input YYWW: 1847
Expected Output: 2018-11-19 [Monday's date in 2018 Week 47]
Thanks in advance
I tried the below but does not work properly
declare @value int = 1519
SELECT CONVERT(VARCHAR(10), DATEADD(YEAR, 2000 + @value / 100-1900, 7 * (@value % 100)-7), 105);
1851 -Expected 17-12-2018[Monday] Works fine for this year 2018
1752 -Expected 25-12-2017[Monday] but shows 24-12-2017 [Sunday]
1652 -Expected 26-12-2016 [Monday] but shows 24-12-2016 [Saturday]
1519 -Expected 04-05-2015 [Monday] but shows 07-05-2015 [Thursday]
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
edited Nov 22 '18 at 9:48
K SuryaPrakash
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
asked Nov 22 '18 at 8:24
K SuryaPrakashK SuryaPrakash
85
85
What attempts have you made so far? What is the lowest value that the first digits can have? Should be, for example, assume 7021 is 1970 or 2070?
– Larnu
Nov 22 '18 at 8:26
Year is always in between 2000 and 2100
– K SuryaPrakash
Nov 22 '18 at 8:27
Also, do you have a calendar table? Depending on where/who you are, the start of the week varies.
– Larnu
Nov 22 '18 at 8:27
Ok, we still need to see your attempt then please.
– Larnu
Nov 22 '18 at 8:28
Edited Post with my trials. Please have a look. Thanks!
– K SuryaPrakash
Nov 22 '18 at 8:43
|
show 2 more comments
What attempts have you made so far? What is the lowest value that the first digits can have? Should be, for example, assume 7021 is 1970 or 2070?
– Larnu
Nov 22 '18 at 8:26
Year is always in between 2000 and 2100
– K SuryaPrakash
Nov 22 '18 at 8:27
Also, do you have a calendar table? Depending on where/who you are, the start of the week varies.
– Larnu
Nov 22 '18 at 8:27
Ok, we still need to see your attempt then please.
– Larnu
Nov 22 '18 at 8:28
Edited Post with my trials. Please have a look. Thanks!
– K SuryaPrakash
Nov 22 '18 at 8:43
What attempts have you made so far? What is the lowest value that the first digits can have? Should be, for example, assume 7021 is 1970 or 2070?
– Larnu
Nov 22 '18 at 8:26
What attempts have you made so far? What is the lowest value that the first digits can have? Should be, for example, assume 7021 is 1970 or 2070?
– Larnu
Nov 22 '18 at 8:26
Year is always in between 2000 and 2100
– K SuryaPrakash
Nov 22 '18 at 8:27
Year is always in between 2000 and 2100
– K SuryaPrakash
Nov 22 '18 at 8:27
Also, do you have a calendar table? Depending on where/who you are, the start of the week varies.
– Larnu
Nov 22 '18 at 8:27
Also, do you have a calendar table? Depending on where/who you are, the start of the week varies.
– Larnu
Nov 22 '18 at 8:27
Ok, we still need to see your attempt then please.
– Larnu
Nov 22 '18 at 8:28
Ok, we still need to see your attempt then please.
– Larnu
Nov 22 '18 at 8:28
Edited Post with my trials. Please have a look. Thanks!
– K SuryaPrakash
Nov 22 '18 at 8:43
Edited Post with my trials. Please have a look. Thanks!
– K SuryaPrakash
Nov 22 '18 at 8:43
|
show 2 more comments
3 Answers
3
active
oldest
votes
1
Try this:
DECLARE @t table(YYWW char(4))
INSERT @t values('1847'),('1752'),('1652'),('1519')
SELECT
CAST(DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0) as date)
FROM @t
Result:
2018-11-19
2017-12-25
2016-12-26
2015-05-04
EDIT:
To get the requested format DD-MM-YYYY:
SELECT
CONVERT(CHAR(10),DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0),105)
FROM @t
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
add a comment |
1
I would, personally, use a calendar table. Then you can do something like:
SELECT YT.YYWW,
CT.[date]
FROM YourTable YT
JOIN CalendarTable CT ON CT.[Year] = '20'+LEFT(YT.YYWW,2)
AND CT.WeekNo = RIGHT(YT.YYWW,2)
AND CT.DayOfWeek = 1; --Assumes Monday is day 1.
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
add a comment |
0
I think your calc may be off week 47 of 2018 starts on 2018-11-26 which is a Monday.
in any case the following should work if you disagree with the above simply subtract 1 from the number of weeks
DECLARE @Date date
DECLARE @Year int = 2000 +18
declare @week int = 47
SET @Date = DATEADD(YEAR, @Year - 1900, 0)
SELECT dateadd(ww,@week-1,DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + (8 - @@DATEFIRST) * 2) % 7, @Date))
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
Your code doesn't give the correct result for1519, the correct result result it2015-05-04, your code returns2015-05-11yes - datefirst is session dependent, but for different servers it has different default values
– t-clausen.dk
Nov 22 '18 at 11:42
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53426636%2fhow-can-i-convert-yyww-to-date-format-based-on-dayex-mondays-date-in-given-yy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Try this:
DECLARE @t table(YYWW char(4))
INSERT @t values('1847'),('1752'),('1652'),('1519')
SELECT
CAST(DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0) as date)
FROM @t
Result:
2018-11-19
2017-12-25
2016-12-26
2015-05-04
EDIT:
To get the requested format DD-MM-YYYY:
SELECT
CONVERT(CHAR(10),DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0),105)
FROM @t
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
add a comment |
1
Try this:
DECLARE @t table(YYWW char(4))
INSERT @t values('1847'),('1752'),('1652'),('1519')
SELECT
CAST(DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0) as date)
FROM @t
Result:
2018-11-19
2017-12-25
2016-12-26
2015-05-04
EDIT:
To get the requested format DD-MM-YYYY:
SELECT
CONVERT(CHAR(10),DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0),105)
FROM @t
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
add a comment |
1
1
1
Try this:
DECLARE @t table(YYWW char(4))
INSERT @t values('1847'),('1752'),('1652'),('1519')
SELECT
CAST(DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0) as date)
FROM @t
Result:
2018-11-19
2017-12-25
2016-12-26
2015-05-04
EDIT:
To get the requested format DD-MM-YYYY:
SELECT
CONVERT(CHAR(10),DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0),105)
FROM @t
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
Try this:
DECLARE @t table(YYWW char(4))
INSERT @t values('1847'),('1752'),('1652'),('1519')
SELECT
CAST(DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0) as date)
FROM @t
Result:
2018-11-19
2017-12-25
2016-12-26
2015-05-04
EDIT:
To get the requested format DD-MM-YYYY:
SELECT
CONVERT(CHAR(10),DATEADD(wk,RIGHT(YYWW,2)+DATEDIFF(d,0,DATEADD(
d,-4,LEFT(YYWW,2)+'0101'))/7,0),105)
FROM @t
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
edited Nov 22 '18 at 10:22
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
answered Nov 22 '18 at 8:45
t-clausen.dkt-clausen.dk
36.5k104482
36.5k104482
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
add a comment |
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
Thanks a lot Clausen!
– K SuryaPrakash
Nov 22 '18 at 9:49
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
t-clausen.dk, One question, is there any code I need to use to format date from YYYY-MM-DD to DD-MM-YYYY? What changes needed in the above script for this format DD-MM-YYYY?
– K SuryaPrakash
Nov 22 '18 at 10:10
add a comment |
1
I would, personally, use a calendar table. Then you can do something like:
SELECT YT.YYWW,
CT.[date]
FROM YourTable YT
JOIN CalendarTable CT ON CT.[Year] = '20'+LEFT(YT.YYWW,2)
AND CT.WeekNo = RIGHT(YT.YYWW,2)
AND CT.DayOfWeek = 1; --Assumes Monday is day 1.
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
add a comment |
1
I would, personally, use a calendar table. Then you can do something like:
SELECT YT.YYWW,
CT.[date]
FROM YourTable YT
JOIN CalendarTable CT ON CT.[Year] = '20'+LEFT(YT.YYWW,2)
AND CT.WeekNo = RIGHT(YT.YYWW,2)
AND CT.DayOfWeek = 1; --Assumes Monday is day 1.
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
add a comment |
1
1
1
I would, personally, use a calendar table. Then you can do something like:
SELECT YT.YYWW,
CT.[date]
FROM YourTable YT
JOIN CalendarTable CT ON CT.[Year] = '20'+LEFT(YT.YYWW,2)
AND CT.WeekNo = RIGHT(YT.YYWW,2)
AND CT.DayOfWeek = 1; --Assumes Monday is day 1.
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
I would, personally, use a calendar table. Then you can do something like:
SELECT YT.YYWW,
CT.[date]
FROM YourTable YT
JOIN CalendarTable CT ON CT.[Year] = '20'+LEFT(YT.YYWW,2)
AND CT.WeekNo = RIGHT(YT.YYWW,2)
AND CT.DayOfWeek = 1; --Assumes Monday is day 1.
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
answered Nov 22 '18 at 8:49
LarnuLarnu
22.6k51933
22.6k51933
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
add a comment |
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
The calendar table you are refering to in your link, is using sunday as day 1 of the week
– t-clausen.dk
Nov 22 '18 at 8:55
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
@t-clausen.dk Calendar Tables are personalised for a business'/user's need and preference; just because the link has Sunday as day 1, doesn't mean that the OP's will. The link is simply a template.
– Larnu
Nov 22 '18 at 9:04
add a comment |
0
I think your calc may be off week 47 of 2018 starts on 2018-11-26 which is a Monday.
in any case the following should work if you disagree with the above simply subtract 1 from the number of weeks
DECLARE @Date date
DECLARE @Year int = 2000 +18
declare @week int = 47
SET @Date = DATEADD(YEAR, @Year - 1900, 0)
SELECT dateadd(ww,@week-1,DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + (8 - @@DATEFIRST) * 2) % 7, @Date))
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
Your code doesn't give the correct result for1519, the correct result result it2015-05-04, your code returns2015-05-11yes - datefirst is session dependent, but for different servers it has different default values
– t-clausen.dk
Nov 22 '18 at 11:42
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
add a comment |
0
I think your calc may be off week 47 of 2018 starts on 2018-11-26 which is a Monday.
in any case the following should work if you disagree with the above simply subtract 1 from the number of weeks
DECLARE @Date date
DECLARE @Year int = 2000 +18
declare @week int = 47
SET @Date = DATEADD(YEAR, @Year - 1900, 0)
SELECT dateadd(ww,@week-1,DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + (8 - @@DATEFIRST) * 2) % 7, @Date))
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
Your code doesn't give the correct result for1519, the correct result result it2015-05-04, your code returns2015-05-11yes - datefirst is session dependent, but for different servers it has different default values
– t-clausen.dk
Nov 22 '18 at 11:42
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
add a comment |
0
0
0
I think your calc may be off week 47 of 2018 starts on 2018-11-26 which is a Monday.
in any case the following should work if you disagree with the above simply subtract 1 from the number of weeks
DECLARE @Date date
DECLARE @Year int = 2000 +18
declare @week int = 47
SET @Date = DATEADD(YEAR, @Year - 1900, 0)
SELECT dateadd(ww,@week-1,DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + (8 - @@DATEFIRST) * 2) % 7, @Date))
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
I think your calc may be off week 47 of 2018 starts on 2018-11-26 which is a Monday.
in any case the following should work if you disagree with the above simply subtract 1 from the number of weeks
DECLARE @Date date
DECLARE @Year int = 2000 +18
declare @week int = 47
SET @Date = DATEADD(YEAR, @Year - 1900, 0)
SELECT dateadd(ww,@week-1,DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + (8 - @@DATEFIRST) * 2) % 7, @Date))
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
edited Nov 22 '18 at 9:07
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
answered Nov 22 '18 at 9:00
RegBesRegBes
47129
47129
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
Your code doesn't give the correct result for1519, the correct result result it2015-05-04, your code returns2015-05-11yes - datefirst is session dependent, but for different servers it has different default values
– t-clausen.dk
Nov 22 '18 at 11:42
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
add a comment |
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
Your code doesn't give the correct result for1519, the correct result result it2015-05-04, your code returns2015-05-11yes - datefirst is session dependent, but for different servers it has different default values
– t-clausen.dk
Nov 22 '18 at 11:42
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
sorry my bad week 47 is actually 2018-11-19 so you will need to subtract 1 from @week - have edited the answer to reflect
– RegBes
Nov 22 '18 at 9:06
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
This will not always return the correct result. @@DATEFIRST is not reliable and should be avoided. It depends of local settings and can give different results on different computers. Usually you need SET DATEFIRST to make it work. Much easier to avoid it
– t-clausen.dk
Nov 22 '18 at 9:38
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
@@DATEFIRST is session not computer dependent, additionally the code works by using @@DATEFIRST to find the first Monday of the year and will work for all session settings.
– RegBes
Nov 22 '18 at 10:45
Your code doesn't give the correct result for
1519, the correct result result it 2015-05-04, your code returns 2015-05-11 yes - datefirst is session dependent, but for different servers it has different default values– t-clausen.dk
Nov 22 '18 at 11:42
Your code doesn't give the correct result for
1519, the correct result result it 2015-05-04, your code returns 2015-05-11 yes - datefirst is session dependent, but for different servers it has different default values– t-clausen.dk
Nov 22 '18 at 11:42
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
Correct, I mistakenly assumed the first week of the year would always have a Monday.
– RegBes
Nov 26 '18 at 6:55
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53426636%2fhow-can-i-convert-yyww-to-date-format-based-on-dayex-mondays-date-in-given-yy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What attempts have you made so far? What is the lowest value that the first digits can have? Should be, for example, assume 7021 is 1970 or 2070?
– Larnu
Nov 22 '18 at 8:26
Year is always in between 2000 and 2100
– K SuryaPrakash
Nov 22 '18 at 8:27
Also, do you have a calendar table? Depending on where/who you are, the start of the week varies.
– Larnu
Nov 22 '18 at 8:27
Ok, we still need to see your attempt then please.
– Larnu
Nov 22 '18 at 8:28
Edited Post with my trials. Please have a look. Thanks!
– K SuryaPrakash
Nov 22 '18 at 8:43