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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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
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