Convert particular string to last day of month-CodePudding

I've got a dataset in SQL Server and I need to transform one of the columns.

The string (nvarchar(max)) is a combination of the year and week of year, so the 8th week of 2021 looks like 202108, the 32nd week of 2019 looks like 201932, and so on.

What I need is to convert this into the last day of the month of the given week, so for 202302, I need 01/31/2023, in datetime format. I've tried a few different methods like CAST() to no avail.

CodePudding user response：

Perhaps this will help, but it really depends on how you count weeks. Perhaps a calendar table would be a better option.

Declare @YourTable Table ([SomeCol] varchar(50))  Insert Into @YourTable Values 
 (202108)
,(201932)
,(202302)
 
Select * 
      ,NewVal = EOMonth(dateadd(week,try_convert(int,right(SomeCol,2)),convert(date,left(SomeCol,4) '0101')))
 from @YourTable

Results

SomeCol NewVal
202108  2021-02-28
201932  2019-08-31
202302  2023-01-31

CodePudding user response：

in datetime format

Once more for those in back: datetime values do not have a human-readable format. Anything you see indicating otherwise is a convenience provided by the tooling.

Your goal at the database level should be to return a basic datetime value, and let the client code worry about the formatting. But we can do that:

EOMonth(DATEADD(week, cast(right([MyColumn],2) as int),CONVERT(datetime, left([MyColumn], 4)   '0101')))

Of course, the main key here is the EOMonth() function. Everything else is just string parsing

CodePudding user response：

data Thanks John Cappelletti

Declare @YourTable Table ([SomeCol] varchar(50));
Insert Into @YourTable
([SomeCol]) Values 
 ('202108')
,('201932')
,('202302');

query

SELECT 
DATEADD(wk,cast(right([SomeCol],2) as int),DATEADD(yy,cast((left([SomeCol],4)) as int)-1900,0))-1 AS WeekEnd
from  @YourTable

dbfiddle