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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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?
January 7, 2021 at 4:31 pm
Yes, I do want to take the month into consideration.
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?
January 7, 2021 at 6:47 pm
Or take the month difference and divide by 12.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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
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 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply