October 21, 2014 at 9:18 am
For the most part this query returns the correct results
SELECT STUFF(CONVERT(varchar(12),DATEADD(wk, -1,GivenDate),107),1,3,DATENAME(MM,DATEADD(month, 2, GivenDate)))
if the givendate is 10/20/2014 the results is "December 22, 2014"
but when it's 10/01/2014, I get "December 24, 2014" when it should be ""December 14, 2014". It seems like any dates from 01 to 09 I get the wrong date but dates after 09 works fine.
October 21, 2014 at 9:28 am
Try passing in your date in an unambiguous form, such as '20141001'.
John
October 21, 2014 at 9:38 am
The problem is that you're getting the month for the "current" date instead of subtracting the week as you do in your convert.
Check this out:
SELECT GivenDate,
STUFF(CONVERT(varchar(12),DATEADD(wk, -1,GivenDate),107),1,3,DATENAME(MM,DATEADD(month, 2, GivenDate))) YourVersion,
STUFF(CONVERT(varchar(12),DATEADD(wk, -1,GivenDate),107),1,3,DATENAME(MM,DATEADD(month, 2, DATEADD(wk, -1,GivenDate)))) CorrectedVersion
FROM (VALUES(CAST( '20141020' AS DATE)),('20141010'),('20141001'))x(GivenDate)
October 21, 2014 at 10:05 am
I'm not clear on the last statement line From (Values........ Also if I use todays date and run the first three lines of code, the CorrectedVersion Date is the same is YourVersion Date which is :
(No column name)YourVersionCorrectedVersion
10/20/2014December 13, 2014December 13, 2014
October 21, 2014 at 10:10 am
The second statement you provide is correct but the only problem is that the year doesn't go up when going into the next year.
for example input: 12/01/2014 the result should be January 24, 2015 but it returns January 24, 2014.
October 21, 2014 at 10:11 am
kd11 (10/21/2014)
I'm not clear on the last statement line From (Values........ Also if I use todays date and run the first three lines of code, the CorrectedVersion Date is the same is YourVersion Date which is :(No column name)YourVersionCorrectedVersion
10/20/2014December 13, 2014December 13, 2014
The last line includes a Table Value Constructor used as a derived table so I can test with different dates.
If you check closely, it will return same values as yours except for days 1-7 which will return the previous month (yours return December and mine returns November).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply