Hi guys,
I have a column StartDate in datetime format and another one called SessionDate in the format YYYY_WW. Somehow I need to get a date from YYYY_WW, so I can calculate the difference between the two dates in years.
So, to replicate this:
declare @StartDate datetime
Set @StartDate=DATEADD(dd, DATEDIFF(dd,0,DATEADD(YEAR,-2,GETDATE())), 0)
select @StartDate as StDate
declare @YearWk varchar(7)
Set @YearWk='2020_10'
select @YearWk as YearWk
Somehow I need to create a column showing the difference between @StartDate and @YearWk in the number of years. I would appreciate any help. Thanks.
January 7, 2021 at 4:15 pm
My first thought would be to simply take the difference of the year. Do you want to adjust based on the month as well as the year?
SELECT LEFT(@YearWk, 4) - YEAR(@StartDate) AS diff_in_years
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 7, 2021 at 4:18 pm
If StartDate was 2018-12-31 and SessionDate was 2020_01, what would be your desired answer as the number of years between the two?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2021 at 4:31 pm
Yes, I do want to take the month into consideration.
January 7, 2021 at 4:42 pm
Yes, I do want to take the month into consideration.
What do you mean by this?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2021 at 4:54 pm
OK. One simplified solution would be as follows:
The existence of leap years makes this an inaccurate solution in some cases, but would it be accurate enough for your purposes?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 7, 2021 at 6:47 pm
Or take the month difference and divide by 12.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 7, 2021 at 7:27 pm
Is WW the week number - or month number? If week number - is it ISO week or US week?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2021 at 8:23 am
This was removed by the editor as SPAM
January 8, 2021 at 8:23 am
This was removed by the editor as SPAM
This is the solution I finally went with. I'll be honest, there is a bit of cut-and-paste from Stackoverflow, which I don't entirely understand, but it is working fine.
DATEDIFF(year,StartDate, DATEADD(week, CAST(REPLACE(RIGHT(session_date,2),'_','') as int),
DATEADD (year, CAST(LEFT(session_date, 4) as int)-1900, 0)) - 4 -
DATEPART(dw, DATEADD(week, CAST(REPLACE(RIGHT(session_date,2),'_','') as int),
DATEADD (year, CAST(LEFT(session_date, 4) as int)-1900, 0)) - 4) + 1
) as Tenure
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply